Problem with LEFT JOIN returning empty set.

[superdevo]

Hey Everybody,

I am working on a project and can't seem to figure out why one SQL statement will work with my MYSQL database and then and almost identical one (based on the syntax) will not.

For instance, this will work:

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg AND visible_prd=1 WHERE idctg_ctg IS NULL  GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

But, this will not:

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg WHERE idctg_ctg IS NULL AND visible_prd=1  GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

By "work", I mean that the first select statement will produce the correct query results, but the second one returns empty results (none what so ever). In fact, if I add ANY OTHER variable in addition to the WHERE idctg_ctg IS NULL part of the clause, then the query results will always return empty.

Any and all help will be greatly appreciated.

Thanks in advance,

Sirian

[pbmods]

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg AND visible_prd=1 WHERE idctg_ctg IS NULL  GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

In this query, visible_prd=1 applies to the rightmost table in the join, and in a left join, it doesn't matter if the right table would return a null result.

Effectively, if `visible_prd`=1 would result in an empty set, then you should only see NULL values for all the fields in products_prd in the join.

But, this will not:

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg WHERE idctg_ctg IS NULL AND visible_prd=1  GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

If this query isn't working, then you're either getting an error (possibly ambiguous `visible_prd` or else unknown column `visible_prd`), or else there are no rows in the join that would satisfy that query, hence your empty set is correct.

By "work", I mean that the first select statement will produce the correct query results, but the second one returns empty results (none what so ever). In fact, if I add ANY OTHER variable in addition to the WHERE idctg_ctg IS NULL part of the clause, then the query results will always return empty.

Keeping in mind that in the first query, the `visible_prd`=1 has no effect whatsoever on the number of rows returned, this makes sense.

My guess is that where `idctg_ctg` IS NULL, nothing else matches the other variables you're trying to match.

Make sure that `idctg_ctg` really is null and not, for example, an empty string.

Without knowing more about your table structure, that's about the furthest I can speculate.