CODEWITHSUNDEEP
sqlmysqljoin
Chris Mccabe
Chris Mccabe

Reputation: 1951

3 table joins one field from one table and one field from another

I have this query 

SELECT cities.city, city_time.time_slot 
FROM cities, city_date, city_time 
WHERE city_time.city_date_id = city_date.city_id = city_date.city_id =  cities.id 
AND city_time.city_date_id = '1'

I'm trying to get cities.city and city_time.time_slot into the same row, they are connected via city_date, 

cities has- 
===========
id
city, 

city_date  has
===========
id
city_id (fk for city_table)

city_time has
==============
city_date_id (FK for city_date table)
time_slot

been trying a few ways-

SELECT cities.city, city_time.time_slot 
FROM (city_date LEFT JOIN cities ON city_time.city_date_id = city_date.city_id ) 
LEFT JOIN city_date ON city_time.city_date_id = city_date.city_id 
WHERE  city_time.city_date_id = '1'

I just want the city field from the cities table to link to the time_slot field in the city_time table. Don't even need the left join but I thought I would include it to help you understand my thinking.

Upvotes: 0

Views: 180

Answers (3)

PiDO
PiDO

Reputation: 291

there should something common in the two table so that you can compare it and have what you wanted to print.. like add something like city id and city time id which has the same record.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

From your other questions, this looks to be what you are after.
Please start using shorter names (hopefully still meaningful) for aliases.

SELECT c.city, t.time_slot 
FROM cities c
LEFT JOIN city_date d on d.city_id = c.id 
LEFT JOIN city_time t on t.city_date_id = d.city_id AND t.city_date_id = '1'

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

select c.city, ct.time_slot
    from cities c
        inner join city_date cd
            on c.id = cd.city_id
        inner join city_time ct
            on cd.id = ct.city_date_id
    where ct.city_date_id = 1

Upvotes: 1

Related Questions