Find rows even if one LEFT JOIN claus matches no rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Find rows even if one LEFT JOIN claus matches no rows

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

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

Post 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 :)
Post Reply