CODEWITHSUNDEEP
mysqlsql
Ali Raza
Ali Raza

Reputation: 71

Bus Route DataBase Query Error

I have 3 tables:

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

I want a bus which passes from both 'Garden' And 'Malir' i.e 2D. I have tried and i succeeded in mapping route of one like if buses passes from garden only but there is a condition that there is two combo box one is from where bus start and second where end .. i want entire route from where bus passes like garden and malir as 1D i mention in above sample is only passing from Malir not from garden so only 2D will shown as a result if Start is from garden and end is on malir .. My Query is Below:

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)

It Giving Empty Result Set

Want a Fix On That.

Upvotes: 0

Views: 312

Answers (2)

Sridhar
Sridhar

Reputation: 503

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)

Upvotes: 2

Taryn
Taryn

Reputation: 247840

You should be able to use the following query:

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)

See SQL Fiddle with Demo

Returns the result:

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

Upvotes: 0

Related Questions