SQL Query challenge

I'm trying to write a MySQL query for my PHP file and wondered if anyone could solve the problem.

It's a little trickier than usual but I'll try to explain it and show what I have come up with already.

Table A fields : ID and Variable
Table B fields : ID and Status

What I would like is a query which shows a count of the number of results we get from Variable being X from table A and where the Status is not StatusA or StatusB.

So far the query which I've come up with is

$Result = mysql_query(
"SELECT COUNT(*) AS count 
FROM TableA 
INNER JOIN TableB ON TableA.ID = TableB.ID 
WHERE TableA.ID = '$X' 
AND TableB.Status !== 'StatusA' || TableB.Status !== 'StatusB'"
);

PHP:

How am I doing with this query?

 

I'm not sure if this way is really any better, but here is how I would write that query:

$Result = mysql_query(
"SELECT COUNT(*) AS count
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.ID
WHERE TableA.ID = '$X'
and (TableB.Status <> 'STatusA' or TableB.Status <> 'StatusB');";

PHP:

As far as I know, !== is not a mysql operator. I hope this helps.


Conor

 

Does it work?

Seems like you;re getting there. Just not sure about the Inner stuff. I always just go "SELECT p.vendors_id, opa.products_options, opa.products_options_values FROM products AS p, orders_products_attributes AS opa, orders_products AS op WHERE opa.orders_id=op.orders_id...etc." So instead of inner, I just go id of this table equals id of that one...

Make sense?

 

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/tps/public_html/Admin/Stockcontrol.php on line 354

That's how I'm doing lol

Actually I didn't mean !== but I've used == which isn't right for mysql. Thanks for that.

 

Ok I've changed the !== to just = and now it doesnt give me an error but it doesnt give me any results. lol

I'll get there eventually.

 

How many statuses do you have? Would it be easier to just say:

where
(tableB.status='statusC' or
tableB.status='statusD' or
tableB.status='statusE' )

If you have a limited amount of status flags, this could work.

Additionally, what is the error message you are getting when you run the other query? Could there be a problem with how you are trying to retrieve the results?

conor

 

Inner joins are used by default - you don't need to specify them explicitly:

SELECT COUNT(*) AS count
FROM TableA, TableB 
WHERE TableA.ID = TableB.ID AND TableA.ID = '$X' AND 
TableB.Status <> 'StatusA' OR TableB.Status <> 'StatusB'

PHP:

J.D.