Find rows even if one LEFT JOIN claus matches no rows
Posted: Mon Sep 29, 2008 10:11 am
A slightly ambiguous title, I'm afraid. Let me propose a scenario to help you understand my problem.
The above table is words (the `block_access` column) which should result in a blocked search. i.e. nothing should be returned. The following SQL should demonstrate that:
However, if the LEFT JOIN finds no rows then the WHERE part of the clause will fail resulting in no rows returned when there might be. If it does find at least 1 row then everything would work perfectly.
Basically, I believe I am probably going about this in the wrong way but am not sure as to how to proceed and would be thankfull for someone much wiser than me on this topic to point me in the right direction. I hope you were unable to understand my problem, I am not sure how I can explain it better.
Code: Select all
Table: block_access
block_id | user_id | block_value
----------------------------------
1 | 134 | foo
2 | 634 | bar
3 | 284 | foobarCode: Select all
SELECT s.whatever
FROM `search` s,
LEFT JOIN `block_access` b ON b.user_id = s.user_id
WHERE b.block_value != '" . mysql_real_escape_string($_POST['search']) . "'
GROUP BY s.user_idBasically, I believe I am probably going about this in the wrong way but am not sure as to how to proceed and would be thankfull for someone much wiser than me on this topic to point me in the right direction. I hope you were unable to understand my problem, I am not sure how I can explain it better.