Trying to get all results from the database using a left join while using WHERE statements

I have done some research, and I think what I am looking for is kind of the opposite to what I've been able to find through searching.

I am currently trying to pull out a bunch of data from one table and match it up with a second. I am using a left join to accomplish this. The code I am using is:

SELECT pn.id FROM pn 
LEFT OUTER JOIN p ON pn.part_number = p.part_number 
WHERE pn.programID = '13' 
AND pn.visible = '1' 
AND pn.type_id = '11' 
AND (p.user_code = '01045' OR p.user_code IS NULL) 
GROUP BY pn.part_number 
ORDER BY p.quantity DESC LIMIT 0,25 

This query works great as long as the user code either matches the provided value or the part number doesn't exist in the p table. The problem I am encountering is that if the part number is in the p table but under a different user code, then the part number doesn't get pulled.

This wouldn't be a problem except I need to display all of the part numbers from the pn table regardless of if they're in the p table. The p holds the quantity which I need to sort by even though I want all the information from the pn t开发者_JAVA技巧able.

Does anyone have any idea how I can get everything from the pn table, while still being able to sort the information by p.quantity even if the part numbers match up but the user codes don't?

Thanks! Andrew

Make the test for p.user_code part of the LEFT JOIN condition instead of doing it in the WHERE clause.

SELECT pn.id FROM pn 
LEFT OUTER JOIN p ON pn.part_number = p.part_number 
    AND p.user_code = '01045'
WHERE pn.programID = '13' 
AND pn.visible = '1' 
AND pn.type_id = '11' 
GROUP BY pn.part_number 
ORDER BY p.quantity DESC LIMIT 0,25