SQL double SELECT on the same field (mysql or sqlite3)

Question

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 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 !

Solution 1

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

Solution 2

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);