Quote:
Originally Posted by superdevo
- "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.
Quote:
Originally Posted by superdevo
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.
Quote:
Originally Posted by superdevo
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.
