left join: all from t1 even when not in t2 2

[Ghodmode]

I have two tables, product and prod_report. I want my query to return a list of product names, IDs, and URLs. If there is an "admin" report for a product, I want it to return the values from that report. Otherwise, I want null values for the report fields.

This seems to me like it should be easily done with a left join, but it's not working for me. The results only include products that have admin reports.

What am I doing wrong?

select
    prod.prod_id,
    prod.name,
    prod.url,
    pr.rank,
    pr.comment,
    pr.report
from
    product prod
    left join prod_report pr on prod.prod_id = pr.prod_id
where
    ( pr.user_id = 'admin' or pr.user_id is null )

Thank you.

--
-- Ghodmode

Give a man a fish and he'll come back to buy more... Teach a man to fish and you're out of business.

 

[spookie]

I suppose you don't need 'or pr.user_id is null ' condition.



--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[Ghodmode]

Even without "[tt]or pr.user_id is null[/tt]", the results are the same. I thought adding that would allow the records to be returned for the products that are not in the second table ...

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

--
-- Ghodmode

Give a man a fish and he'll come back to buy more... Teach a man to fish and you're out of business.

 

[TonyGroves]

You need to move the 'admin' condition into the join condition:
[tt]
select
prod.prod_id,
prod.name,
prod.url,
pr.rank,
pr.comment,
pr.report
from
product prod
left join prod_report pr
on prod.prod_id = pr.prod_id and pr.user_id = 'admin'
[/tt]

 

[guelphdad]

An easy thing to remember, if you are using a left join and you use a where condition, you have effectively made your join into an inner join. the solution is as Tony says, change the where to an and and place it in your on clause.