SQL double SELECT on the same field (mysql or sqlite3)
I have a problem that I cannot solve! (sqlite3, but I think it will be the same as MySql) I have these tables (image):
alt text http://www.radarkiller.fr/blog/bddprobleme.png
And I would like to find all tires (with type and number) that go to two different streets, of which I have a street_id (for example 12 and 14).
The result should provide the customer with all buses (ID, type and number) in the city that go from street 12 to street 14 (example).
Like Larry Lustig, you can make a shortcut with two adjacent foreign keys.
Thank you in advance for your help!
source to share
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
source to share
Assuming you want to have three separate records in your sampling result set and assuming all the "neighborhood" 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);
source to share