[thelist] SQL left join - it works, but why?

[Adam Patrick]

> 
> SELECT a.prodId, a.catId, b.catId
>   FROM  products_categories a
>          LEFT JOIN products_categories b ON (a.prodId = b.prodId AND b.catId
> != a.catId)
> 
>  WHERE a.catId= '5' AND b.catId IS NULL;
> 
> What I don't understand is why!
> 
> But if a prodId has several different catIds, one of which is my selected
> category, when I do the join why don't I get a null for that prodId where
> a.catId = b.catId?
> 
> Does a row in SQL depend on the other rows (ie will return a null for a left
> join only if no other rows are returned for that record in the first table),
> or am I (as usual) thinking very hard in the wrong direction :)?

You are correct here.  an outer join will return a blank row (2nd table)
only for rows (in the 1st table) that don't match.  if there is a match,
it will only return the match.  

One thing in your  query that may be confusing you is that you are
saying b.catid must be NULL. BUT if b.catID is null, then b.catID !=
a.catId will always evaluate to false since NULL values can't be tested
for equivalence.

so maybe what's probably possibly could be happening is that the outer
join is working but with (b.catID is null) you're throwing away those
rows that match (b.catid != a.catid).

if this doesn't help, maybe include some results you're getting.  that
would help.