公交路线DataBase查询错误

Question

我有3张桌子：

Buses:
    id   |   bus_name
    1    |   2D
    2    |   1D

Routes:
id   |   route_name
1    |   Garden
2    |   Malir

Bus_Route
id......|....bus_id...|....route_id
1.......|......1......|....1   
2.......|......1......|....2   
3.......|......2......|....1

我想要一辆从'Garden‘和'Malir’都经过的巴士，也就是2D。我试过了，我成功地绘制了一条路线，比如公交车只从花园经过，但有一个条件是有两个组合框，一个是公交车的起点，第二个是终点。我想从像花园和马利尔公交车通过的整个路线，因为1D我在上面提到的样本只从马利尔通过，而不是从花园，所以只有2D将显示结果，如果起点是从花园和结束是马利尔。我的问题如下：

select b.bus_name 
from buses b, route r, bus_route br 
where (b.id = br.bus_id AND r.id = br.route_id) 
  AND (r.id=1 AND r.id=2)

它给出的结果集为空

想要解决这个问题。

Answer 1

SELECT bus_name
FROM buses
WHERE id in
(SELECT b.bid
FROM bus_route b
JOIN bus_route _b  
ON b.bid = _b.bid  
WHERE b.rid = 1 AND _b.rid = 2)

Answer 2

您应该能够使用以下查询：

select *
from buses b
left join bus_route br
  on b.id = br.bus_id
where br.route_id = 1
  and exists (select *
              from buses b1
              left join bus_route br2
                on b1.id = br2.bus_id
              where br2.route_id=2
               and b.id = b1.id)

请参阅SQL Fiddle with Demo

返回结果：

| ID | BUS_NAME | BUS_ID | ROUTE_ID |
-------------------------------------
|  1 |       2D |      1 |        1 |