SQL Query challenge

[Weirfire]

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'"
);

How am I doing with this query?

[thedagda]

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');";


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


Conor

[T0PS3O]

How am I doing with this query?

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?

[Weirfire]

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.

[Weirfire]

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. :)

[thedagda]

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

[J.D.]

$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'"
);How am I doing with this query?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'J.D.