SQL Join Statement

Question

Hi

I have the following SQL SELECT statement

SELECT bar_id, bar_name, town_name, advert_text     
FROM bar, towns, baradverts
WHERE town_id = town_id_fk
AND bar_id = bar_id_fk

My problem is that since not every bar has an advert in table "baradverts", these bars are not coming up in the results. In other words I need a NULL for those bars that do not have an advert string.

Answer 1

It's hard to tell which column goes with which table, but I think this is what you are looking for. If you post your schema that would help.

SELECT bar_id, bar_name, town_name, advert_text      
FROM bar b
inner join towns t on t.town_id = b.town_id_fk 
left outer join baradverts ba on t.town_id = ba.town_id_fk 
    AND b.bar_id = ba.bar_id_fk 

Answer 2

you may find this helpful reading:

Understanding SQL Joins