SQL join query -- two tables-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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