How to fix error in joining two tables together

Question

This is my first time ever working with sql and my first task is joining two tables together. I saw a similar example of how its done which worked and tried it for this example but it did not work. I am not sure why I am getting an error

SELECT os.city_id
        ,os.park_id AS slide_id
from om_slide AS os
where os.city_id = 12
LEFT JOIN ga_park gt ON (os.park_id=gt.park_id)

I know it works up to the where os.city_id = 12. that gives me a result. Just my left statement is not working and I do not know why

`from om_slide AS os LEFT JOIN ga_park gt ON (os.park_id=gt.park_id) where os.city_id = 12` — John Woo, Dec 13 '13 at 18:01
Why are you joining at all here? I can't see where you've selected anything from the other table. — cms_mgr, Dec 13 '13 at 18:03

Shiva · Answer 1 · 2013-12-13T18:57:35.683

2

If you just want info from the om_slide table for a given city_id, the following should work.

SELECT os.city_id
        , os.park_id AS slide_id
FROM om_slide AS os
WHERE os.city_id = 12

If you want additional info from the ga_park table also, for a given city_id, then use the following.

SELECT os.city_id
        , os.park_id AS slide_id
        , gt.park_name -- or whatever additional field(s) you want from ga_park
FROM om_slide AS os LEFT JOIN ga_park AS gt ON os.park_id = gt.park_id
WHERE os.city_id = 12

edited Dec 13 '13 at 18:57

answered Dec 13 '13 at 18:02

Shiva

20,575
14
82
112

Isn't the `JOIN` still superfluous here? What is it selecting? – cms_mgr Dec 13 '13 at 18:14
Guessing that OP's eventual goal is to get additional data from the `ga_park` table. If it's just the `park_id` for a given `city_id`, then you are right, there is no need to join `om_side` and `ga_park` tables, OP could just query the `om_slide` table. – Shiva Dec 13 '13 at 18:52

score 1 · Accepted Answer · edited May 23 '17 at 10:32

Your preferences may differ but I find explicit joins easier to read. Your LEFT JOIN may appear as though it's not working because you aren't selecting anything from that table. Subject to my comment to that effect on the question I'd go with something like:

SELECT
    os.city_id, os.park_id AS slide_id, gt.*
FROM
    om_slide AS os
LEFT OUTER JOIN
    ga_park AS gt
ON
    os.park_id=gt.park_id
WHERE
    os.city_id = 12

How to fix error in joining two tables together

2 Answers2