CODEWITHSUNDEEP
mysql
lisovaccaro
lisovaccaro

Reputation: 33956

Add WHERE clause to just one table on Left Join?

I'm joining Sites with History and selecting the rows that aren't in History.

$data = mysql_query("
        select * from Sites
        left join History
        on Sites.URL = History.URL
        where History.URL is null
        order by Karma
        ");

The problem is that I want to add WHERE UID = $uid to table History so I only join rows with said UID.

I want to do something like this:

    select * from Sites
    left join History where UID = $uid
    on Sites.URL = History.URL
    where History.URL is null
    order by Karma

Is this possible? How can I do this?

Upvotes: 2

Views: 762

Answers (3)

Jason McCreary
Jason McCreary

Reputation: 72991

You can add this to the JOIN clause:

select * from Sites
left join History ON UID = $uid
AND Sites.URL = History.URL
where History.URL is null
order by Karma

or the WHERE clause:

select * from Sites
left join History
AND Sites.URL = History.URL
where History.URL is null
and UID = $uid
order by Karma

Note: You should prefix this with the appropriate table name. I would have but you didn't specify.

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146499

Yes this way:

select * from Sites s
Where Not Exists
   (Select * From History
    Where URL Is Null  
       And uid = $uid)  
order by Karma

Upvotes: 1

Jordão
Jordão

Reputation: 56477

Try with a not exists clause:

select * from Sites
where not exists (
  select * from History
  where UID = $uid
  and Sites.URL = History.URL)
order by Karma

Upvotes: 0

Related Questions