SQL double SELECT on the same field (mysql or sqlite3)
i have a problem that i can't solve ! (sqlite3, but i think it would be the same than MySql) I have those tables (image) :
alt text http://www.radarkiller.fr/blog/bddprobleme.png
And i would like to find all the bus lines (with type and number) that go to two 开发者_JAVA技巧different streets from which i have the street_id (12 and 14 for exemple).
The result shall give to the customer all of the bus lines (the id, type and number) in the city which go from the street n°12 to the street n°14 (example).
Like Larry Lustig said you can make a shortcut with the two neighborhood foreign keys.
Thank you in advance for your help !
Using GROUP BY/COUNTING:
SELECT t.bus_line_id,
t.bus_line_type,
t.bus_line_number
FROM BUS_LINE t
JOIN LINK_BUS_NEIGBOURHOOD lbn ON lbn.bus_line_id = t.bus_line_id
JOIN LINK_NEIGHBOURHOOD_STREET lns ON lns.neighbourhood_id_ns = lbn.neighbourhood_id_ns
JOIN STREET s ON s.street_id = lns.street_id
WHERE s.street_id IN (12, 14)
GROUP BY t.bus_line_id,
t.bus_line_type,
t.bus_line_number,
s.street_id
HAVING COUNT(DISTINCT s.street_id) = 2
Using JOINs:
SELECT t.bus_line_id,
t.bus_line_type,
t.bus_line_number
FROM BUS_LINE t
JOIN LINK_BUS_NEIGBOURHOOD lbn ON lbn.bus_line_id = t.bus_line_id
JOIN LINK_NEIGHBOURHOOD_STREET lns ON lns.neighbourhood_id_ns = lbn.neighbourhood_id_ns
JOIN STREET s ON s.street_id = lns.street_id
AND s.steet_id = 12
JOIN STREET s2 ON s2.street_id = lns.street_id
AND s2.steet_id = 14
Assuming you want three separate records in your sample result set and assuming that all the "neighborhood_id" columns are FKs back to the neighborhood table, try:
SELECT *
FROM bus_line
WHERE EXISTS (SELECT *
FROM neighborhood N
INNER JOIN link_neighborhood_street S ON N.neighborhood_id = S.neighborhood_id_ns
WHERE S.street_id = 12)
AND EXISTS (SELECT *
FROM neighborhood N
INNER JOIN link_neighborhood_street S ON N.neighborhood_id = S.neighborhood_id_ns
WHERE S.street_id = 14);
精彩评论