MySQL Join Question

Need help with a join

[Sekka]

Can't seem to figure this out.

I have a MySQL query where I have Table A joined to Table B.

Now, I need the rows from A more than I do B, put another way, I need the rows from Table A no matter what happens getting rows from Table B.

Again, put another way, no matter whether a record doesn't exist in B, does exist in B but doesn't meet the WHERE criteria, or anything at all, I need the records from A no matter what.

I have tried using LEFT JOIN and LEFT OUTER JOIN but they both don't work when records exist in B but don't meet the where criteria, therefore the JOIN fails and I lose the record from A.

Is there anyway to get the JOIN I need in one query or will I need to use more than one?

[leadegroot]

Have you put an ON on table B?
eg
SELECT *
FROM tableA As a
LEFT OUTER JOIN tableB AS b ON b.id = a.id
WHERE a.field = 'something'
?
:)

The 'ON' clause is like a 'WHERE' that only applies to the table in that JOIN line

[Sekka]

Yup.

SELECT * FROM cp_users AS T1  
INNER JOIN cp_workgroups AS T2  
ON T2.group_id = T1.group_id  
LEFT JOIN cp_permissions AS T3  
ON T3.group_id = T2.group_id 
WHERE T1.user_id = %s  
AND T1.user_pass = '%s'  
AND T1.active = 1  
AND T1.deleted = 0  
AND T2.active = 1  
AND T2.deleted = 0  
AND T3.deleted = 0  
GROUP BY T1.group_id  
LIMIT 1

This works fine when no permission records exist for a certain group.

The problem arises when a permission record exists and has its "deleted" property set to 1, failing the WHERE criteria. Because no other record exists to match the criteria, the JOIN for that group_iud fails.

I need to keep that record from T1 though.

[syber]

When you do a JOIN you are doing s set operation. The result of the JOIN is a new set. The WHERE clause then works on this new set. So you are selecting the rows you need in the JOIN only to eliminate them in the WHERE clause. To fix this you need to do the following:

 SELECT * 
 FROM a LEFT OUTER JOIN b 
  ON a.id = b.id AND b.something > 0 

By moving the WHERE condition to the ON clause, you will be assured of getting every row from table A no matter what.

[Sekka]

Did not know you could do that.

Thank you for your help, works perfectly, and you have enlightened me!