Page 1 of 1

Find rows even if one LEFT JOIN claus matches no rows

Posted: Mon Sep 29, 2008 10:11 am
by someberry
A slightly ambiguous title, I'm afraid. Let me propose a scenario to help you understand my problem.

Code: Select all

Table: block_access
 
block_id   | user_id    | block_value
----------------------------------
1          | 134        | foo
2          | 634        | bar
3          | 284        | foobar
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:

Code: 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_id
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.

Re: Find rows even if one LEFT JOIN claus matches no rows

Posted: Tue Sep 30, 2008 5:41 am
by VladSun
someberry wrote:I hope you were unable to understand my problem
:)

Really, I couldn't clearly understand what your problem is. Maybe this would be a solution:
[sql] SELECT    search.*FROM            searchLEFT JOIN     block_access ON         block_access.user_id = search.user_id         AND         block_access.block_value = '" . mysql_real_escape_string($_POST['search']) . "'WHERE    block_access.user_id IS NULL [/sql]

Re: Find rows even if one LEFT JOIN claus matches no rows

Posted: Tue Sep 30, 2008 8:50 am
by someberry
Thanks VladSun. I had previously tried adding the second WHERE on the LEFT JOIN but did not try adding the block_access.user_id IS NULL in. I had to add block_access.user_id = '' as well though since it sometimes oddly returned a blank string.

But all's well now. Thank you :)