SQL join query -- two tables

Question

I have two tables buses and passengers:

create table buses (id
integer primary key, origin varchar not null, destination varchar not null, time varchar not null, unique
(origin, destination, time) ); 
create table passengers ( id integer primary key, origin varchar not null,
destination varchar not null, time varchar not null );
insert into buses (id, origin, destination, time) values (10, 'Warsaw', 'Berlin' , '10:55');
insert into buses (id, origin, destination, time) values (20,  'Berlin' , 'Paris' , '06:20');
insert into buses (id, origin, destination, time) values (21,  'Berlin' , 'Paris' , '14:00');
insert into buses (id, origin, destination, time) values (22,  'Berlin' , 'Paris' , '21:40');
insert into buses (id, origin, destination, time) values (30, 'Paris', 'Madrid' , '13:30');

insert into passengers (id, origin, destination, time) values (1 , 'Paris' , 'Madrid' , '13:30');
insert into passengers (id, origin, destination, time) values (2 , 'Paris' , 'Madrid' , '13:31');
insert into passengers (id, origin, destination, time) values (10 , 'Warsaw', 'Paris' , '10:00');
insert into passengers (id, origin, destination, time) values (11 , 'Warsaw', 'Berlin', '22:31');
insert into passengers (id, origin, destination, time) values (40 , 'Berlin', 'Paris' , '06:15');
insert into passengers (id, origin, destination, time) values (41 , 'Berlin', 'Paris' , '06:50');
insert into passengers (id, origin, destination, time) values (42 , 'Berlin', 'Paris' , '07:12');
insert into passengers (id, origin, destination, time) values (43 , 'Berlin', 'Paris' , '12:03');
insert into passengers (id, origin, destination, time) values (44 , 'Berlin', 'Paris' , '20:00');

For each bus, I want to return the number of passengers boarding it. Just two columns the id and passengers on board.

10 -> 0 20 -> 1 21 -> 3 22 -> 1 30 -> 1

Explanation: -- Bus id: 10 moves from Warsaw to Berlin and departs at 10:55 but a passenger-only boarded once at 22:31. So, no passengers have boarded the bus: 10 and hence 0 passengers. -- Bus id: 20 from Berlin to Paris starts at 06:20 and only one passenger has boarded it at 06:15. So, 1 passenger has boarded the bus: 20.

The join condition would be multiple columns of origin and destination I guess and I am not sure how to handle the time column and specify the conditions and write the WHERE clause

```
SELECT
  b.id, count(*),
FROM buses b
JOIN passenger p
  ON p.origin=b.origin
    AND p.destination=e.destination; 
```

Answer 1

This question and query needs some refinement. I think that the bus is travelling and hence there should be a depart time and arrival time. The same should be in the other table as well. Then you can count the number of passengers in that time. However, the query you wrote also had some errors and if you want the exact time match then here is the below mentioned query

SELECT B.id, COUNT(*)
FROM buses b
JOIN passengers p
ON p.origin=b.origin
AND p.destination=b.destination
AND p.time=b.time; 

Try this and let me know

Answer 2

I think if you change the variable type of column time to datetime, You can compare the time and get output by using this query,

SELECT b.id, COUNT(*) FROM buses b JOIN passengers p ON p.origin = b=origin AND p.destination = b.destination and p.time > b.time

Answer 3

Use this:

Select b.id,isnull(Count(p.id),0) as Total from buses b ,passengers p where b.id=p.id
    and b.time >=p.time 
    group by p.id, b.id

Answer 4

SELECT buses1.id,
       (SELECT Count(passengers.id)
        FROM   passengers
        WHERE  buses1.origin = passengers.origin
               AND buses1.destination = passengers.destination
               AND passengers.time <= buses1.time
               AND Ifnull((SELECT buses2.time
                           FROM   buses AS buses2
                           WHERE  buses2.origin = buses1.origin
                                  AND buses2.destination = buses1.destination
                                  AND buses2.time < buses1.time
                           ORDER  BY buses2.time DESC
                           LIMIT  1), '00:00') < passengers.time) AS COUNT
FROM   buses AS buses1
ORDER  BY buses1.id; 

Answer 5

Use This :

WITH CTE AS(
            select busId, count(*) as no_of_passenger 
            from (select x.busId,
                        x.psgid,
                        Row_number() over( partition by x.psgId order by x.bustime) as rnk 
                  from (SELECT  b.id as busId,
                                p.id as psgId,
                                p.time as pagtime,
                                b.time as bustime
                        FROM buses b 
                             JOIN passengers p 
                        ON p.origin = b.origin
                            AND p.destination = b.destination 
                            and (p.time < b.time OR p.time=b.time))x
                  )y 
             where y.rnk=1            
             group by busId ) 
select  b.id,
        ISNULL(c.no_of_passenger,0)  
from CTE c Right Join buses b on b.id=c.busId 
order by b.id  

Answer 6

select bb.Id, count(pb.id) as count 
    from buses bb 
    left join passengers pb
    on (bb.origen = pb.origen) and 
         (bb.destino = pb.destino) and (pb.time <= bb.time)
         where not exists 
         (select b.Id, b.time, b.origen, b.destino 
           from buses b 
             left join passengers p
              on (b.origen = p.origen) and 
                (b.destino = p.destino) and (p.time <= b.time)
            where (b.time < bb.time) and (b.origen = bb.origen) and (b.destino = bb.destino) 
      and (p.id = pb.id) 
    )
  group by bb.id
  order by bb.id

Answer 7

select buses.id,coalesce(temp_3.no_passengers,0) 
from buses 
left join 
   (select temp_2.id,count(*) as no_passengers 
    from 
     (select 
        temp.id,
        temp.p_id,
        temp.origin,
        temp.destination,
        temp.bus_time,
        temp.p_time,
        (temp.bus_time - temp.p_time) as checking,
        min(temp.bus_time - temp.p_time) over (partition by temp.p_id) as fi 
           from
            (select 
                a.id,b.id as p_id,
                a.origin, 
                a.destination,
                ((cast(substr(a.time,1,2) as int)*60) + cast(substr(a.time,4) as int)) as bus_time,
                ((cast(substr(b.time,1,2) as int)*60) + cast(substr(b.time,4) as int)) as p_time 
                from buses a 
                left join passengers b 
                  on ((a.origin = b.origin) and (a.destination=b.destination)
                  and (bus_time>=p_time))) temp) temp_2 
where temp_2.checking = temp_2.fi group by temp_2.id) temp_3 on (buses.id=temp_3.id) 
order by buses.id;