Joint inquiry question!

youg21

I am a beginner, and I have come to ask for advice. Urgent!

Two tables about train information
T_Schedule
ScheduleCode varchar(10)
fromCity varchar(20)
toCity varchar(20)
LeaveTime datetime
ArrivalTime datetime
TrainType varchar(10)
Speed ​​varchar(10)
Distance int
Days int


T_Schedule_Detail
cid int non-empty primary key
ScheduleCode varchar(10)
cityName varchar(20)
ArrivalTime datetime
LeaveTime datetime
NumOfSchedule int
Distance int
Days int



Question: If you cannot reach the terminal directly from the departure station, you need to transfer..
      Provide the name of the origin and destination
Request to find all the transfer station names that can go from station A to station B

michealhenry

Is there no comment on the table?

What about the relationship between the two tables?

youg21

T_Schedule_Detail.ScheduleCode is the foreign key of T_Schedule.ScheduleCode

torechary

It’s best to post the data too~

youg21

Notes to Table 1:
Car number
Departure station
Terminal
departure time
Time of arrival
Train type
Speed
Total mileage
Driving days


Table 2
cid primary key ID
Car number
Site name of the route of this train
Arrival time at this site
Time to leave this site
The order of this platform on the travel line
Completed mileage
Days completed

youg21

Data in the table (partially incomplete)
Table 1
ScheduleCode,fromCity,toCity,LeaveTime,ArrivalTime, TrainType, Speed,Distance,Days
T108 Beijing Hainan
T989 Changsha Chongqing


Table 2
cid ScheduleCode cityName ArrivalTime LeaveTime NumOfSchedule Distance Days
1 T108 Beijing 11:03 11:30 1 0 0
2 T108 Zhengzhou 12:09 12:30 2 689 1
3 T108 Changsha 18:00 18:05 3 1486 1
4 T108 Hainan 14:45 14:45 4 4469 3
5 T989 Changsha 9:00 9:20 1 0 0
6 T989 Chongqing 12:00 12:00 2 1445 1


The meaning of the question is if the starting station is Beijing and the final station is Chongqing, how to find the transfer station (Changsha)

michealhenry

select a.ScheduleCode ,a.cityname ,b.ScheduleCode, b.cityname
from T_Schedule_Detail a left join T_Schedule_Detail b
     on a.cityName = b.cityName and a.ScheduleCode <> b.ScheduleCode
where exists (select 1 from T_Schedule_Detail
              where ScheduleCode = a.ScheduleCode and cityName ='Beijing') and
      exists (select 1 from T_Schedule_Detail
              where ScheduleCode = b.ScheduleCode and cityName ='Chongqing)

michealhenry

-Build a table (only create related fields)
create table T_Schedule_Detail
(
cid int,
ScheduleCode varchar(4),
cityName varchar(10)
)

insert into T_Schedule_Detail select 1,'T108','Beijing'
insert into T_Schedule_Detail select 2,'T108','Zhengzhou'
insert into T_Schedule_Detail select 3,'T108','Changsha'
insert into T_Schedule_Detail select 4,'T108','Hainan'
insert into T_Schedule_Detail select 5,'T989','Changsha'
insert into T_Schedule_Detail select 6,'T989','Chongqing'

--Inquire
select a.ScheduleCode ,a.cityname ,b.ScheduleCode, b.cityname
from T_Schedule_Detail a left join T_Schedule_Detail b
     on a.cityName = b.cityName and a.ScheduleCode <> b.ScheduleCode
where exists (select 1 from T_Schedule_Detail
              where ScheduleCode = a.ScheduleCode and cityName ='Beijing') and
      exists (select 1 from T_Schedule_Detail
              where ScheduleCode = b.ScheduleCode and cityName ='Chongqing')

--result
ScheduleCode cityname ScheduleCode cityname
------------ ---------- ------------ ----------
T108 Changsha T989 Changsha

(The number of rows affected is 1 row)

--It means: take T108 times and transfer to T989 times in Changsha

mill1128

Yeah, master, okay