How to use LEFT JOIN in this query

Question

I have the following query

SELECT
    interview_content.title `title`,
    gallery_images.img_path img
FROM
    `interview`,
    `interview_content`,
    `gallery`,
    `gallery_images`
WHERE
    `gallery_images`.`id_parrent` = `gallery`.`id`
AND
    `gallery`.`id` =  `interview`.`gallery_id`
AND
    `gallery_images`.`default` = '1'
AND
    `interview`.`id` = `interview_content`.`id_parrent`
AND
    interview_content.id_lang = '2'
ORDER BY
    interview.date DESC

I need to use LEFT JOIN on gallery, gallery_images tables (to be able to get interview.title even when there is no gallery or default image)

Thanks much

UPDATE

The problem, is in second LEFT JOIN. I can't make them to work together correctly. When i try

SELECT
    interview_content.title `title`,
    gi.img_path img
FROM
    `interview`,
    `interview_content`
LEFT JOIN `gallery` as g ON g.`id` = `interview`.`gallery_id` 
LEFT JOIN `gallery_images` as gi ON gi.id_parrent = g.`id`
WHERE
    `interview`.`id` = `interview_content`.`id_parrent`
AND
    interview_content.id_lang = '2'
ORDER BY
    interview.date DESC

it returns //Unknown column 'interview.gallery_id' in 'on clause'

Can you show your tables? because maybe its not only your query and it can be done easier with changes in your tables — Yoram de Langen, Feb 17 '11 at 07:46
@Tricker The problem was in ANSI format mixing. See my answer — Simon, Feb 17 '11 at 08:01

score 3 · Answer 1 · answered Feb 16 '11 at 17:50

3

You know what you need to do. Google is your friend?

http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join

answered Feb 16 '11 at 17:50

user606723

4,918
2
27
34

why Google, when SO is full of questions like this? http://stackoverflow.com/questions/3655716/learning-left-join-in-mysql – Alex Feb 16 '11 at 17:54
The problem was in another place. See my answer – Simon Feb 17 '11 at 07:56

score 1 · Answer 2 · answered Feb 16 '11 at 17:56

1

Try that one:

SELECT
    interview_content.title `title`,
    gallery_images.img_path img
FROM
    `interview`,
    `interview_content`,
LEFT JOIN `gallery` ON (`interview`.`gallery_id` = `gallery`.`id` AND `gallery_images`.`default` = '1'), 
LEFT JOIN `gallery_images` ON `gallery`.`id` = `gallery_images`.`id_parrent`
WHERE
    `interview`.`id` = `interview_content`.`id_parrent`
AND
    interview_content.id_lang = '2'
ORDER BY
    interview.date DESC;

answered Feb 16 '11 at 17:56

w.k

8,218
4
32
55

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN `gallery` ON (`interview`.`gallery_id` = `gallery`.`id` AND `gallery_i' at line 7 – Simon Feb 17 '11 at 07:20
You have to remove the `,` after `interview_content` – Yoram de Langen Feb 17 '11 at 07:35

score 1 · Accepted Answer · edited May 23 '17 at 12:29

Don't mix ANSI-89 style and ANSI-92 style joins.

I found same question in SO, here.

So my query must look like this

SELECT
    ic.title `title`,
    gi.img_path img
FROM
    `interview` i
JOIN interview_content ic  on ic.id_parrent =  i.id AND ic.id_lang = '2'
LEFT JOIN `gallery` as g ON g.`id` = i.`gallery_id` 
LEFT JOIN `gallery_images` as gi ON gi.id_parrent = g.`id`
AND gi.`default` = '1'
WHERE
    i.`id` = ic.`id_parrent`
OPRDER BY
    `i`.`date` DESC

How to use LEFT JOIN in this query

3 Answers3