CODEWITHSUNDEEP
mysqlleft-joininner-join
mikatakana
mikatakana

Reputation: 523

MySQL: How can I INNER JOIN with LEFT JOIN results

I have a table items which is the points on the map. They have list of the nearest subway stations in table item_stations (item_id, station_id). The names these stations are placed in table stations (id, station_name, station_direction, ...).

So I need to return in SQL-query item-data with the names of the nearest stations.

SELECT 
    i.id, i.title, i.description,
    GROUP_CONCAT(s.station_id) as stations_ids,
FROM items i
    LEFT JOIN item_stations s ON s.item_id = s.id
WHERE id = ?
GROUP BY i.id
LIMIT 1

How I must write INNER JOIN WITH table stations ON stations.id = s.station_id?

Upvotes: 0

Views: 81

Answers (1)

Parandroid
Parandroid

Reputation: 513

Probably you want something like this?

SELECT 
    i.id, i.title, i.description,
    GROUP_CONCAT(s.station_id) as stations_ids,
    GROUP_CONCAT(stations.station_name) as stations_names
FROM 
    items i
LEFT JOIN 
    item_stations s ON s.item_id = s.id
INNER JOIN stations on
    stations.id = s.station_id
WHERE i.id = ?
GROUP BY i.id, i.title, i.description
LIMIT 1

I placed i.id in WHERE block, cause id field is in the 2 tables - item_stations and items. Mb that was a problem?

Upvotes: 2

Related Questions