CODEWITHSUNDEEP
phpmysql
Iris
Iris

Reputation: 63

How to select route records based on same bus

I have a MYSQL table structure like:

+----+--------+-------------+------------+
| id | bus_id | board_point | drop_point |
+----+--------+-------------+------------+
|  1 |      1 | Toronto     | Toronto    |
|  2 |      1 | Toronto     | Ottawa     |
|  3 |      1 | Ottawa      | Montreal   |
|  4 |      1 | Montreal    | Quebec     |
| 10 |      3 | Whistler    | Banff      |
| 12 |      2 | Toronto     | Montreal   |
| 16 |      2 | Toronto     | Toronto    |
+----+--------+-------------+------------+

I would like to search board_point and drop_point if they have same bus_id. For example:

  1. board_point = Toronto and drop_point = Quebec, the result is bus 1.
  2. board_point = Toronto and drop_point = Montreal, the result is bus 1 and 2.
  3. board_point = Toronto and drop_point = Banff, the result is no record because they are not in same bus.

If I only use where board_point = X or drop_point = Y, the result is not correct. My question is how to select * from route where board_point = X or drop_point = Y and they must in same bus_id.

Thanks a lot!

Upvotes: 0

Views: 84

Answers (3)

user2560539
user2560539

Reputation:

Use MySQL IF like in the example queries in order to give a positive value in case the board_point or drop_point have the specific value you seek.

You need results when both conditions are met, meaning when both found_in_board,found_in_drop, in the example queries below, are positive

SELECT `busid`,
         SUM(IF(`board` = "Toronto",1,0)) AS `found_in_board`,
         SUM(IF(`drop` = "Montreal",1,0)) AS `found_in_drop`
  FROM `buses`
  GROUP BY `busid`
  HAVING `found_in_board`  > 0
     AND `found_in_drop`  > 0;

SELECT `busid`,
         SUM(IF(`board` = "Toronto",1,0)) AS `found_in_board`,
         SUM(IF(`drop` = "Banff",1,0)) AS `found_in_drop`
  FROM `buses`
  GROUP BY `busid`
  HAVING `found_in_board`  > 0
     AND `found_in_drop`  > 0;

You can check the output of the above queries in SQL Fiddle

Upvotes: 0

Harshil Doshi
Harshil Doshi

Reputation: 3592

select t.bus_id
from
   (
    select bus_id,drop_point
    from route
    where bus_id in (select distinct bus_id
                    from route
                    where board_point = 'Toronto'
                   )     
   )t
where t.drop_point = 'Quebec'
;

New DEMO

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use conditional aggregation 

  SELECT bus_id,
         COUNT(CASE WHEN board_point = 'Toronto' THEN 1 END) as total_board,
         COUNT(CASE WHEN drop_point  = 'Quebec'  THEN 1 END) as total_drop
  FROM yourTable
  GROUP BY bus_id
  HAVING COUNT(CASE WHEN board_point = 'Toronto' THEN 1 END)  > 0
     AND COUNT(CASE WHEN drop_point  = 'Quebec'  THEN 1 END)  > 0

Upvotes: 4

Related Questions