Table joining question. I want to join these two tables into this result. Can anyone help me?
-
Suppose I have two tables called TableA and TableB
TableA
portid acid adDate adTime adPort adType
----------- ---------- ---------- ---------- ---------- ------
1 0200002 10/11/2007 10:30 HKG A
2 0200002 11/11/2007 10:30 HKG A
3 0200002 12/11/2007 10:30 HKG A
4 0200002 13/11/2007 10:30 HKG A
5 0200002 14/11/2007 10:30 HKG A
TableB
portid acid adDate adTime adPort adType
----------- ---------- ---------- ---------- ---------- ------
6 0200002 22/11/2007 10:30 MAC D
7 0200002 23/11/2007 09:30 HKG D
8 0200002 24/11/2007 11:30 TAI D
What I want to do is joining these two tables into these result:
acidA adDateA adTimeA adPortA adTypeA acidB adDateB adTimeB adPortB adTypeB
---------- ---------- --------- ---------- ------ ------- ---------- ---------- --------- ----------
0200002 10/11/2007 10:30 HKG A 0200002 22/11/2007 10:30 MAC D
0200002 11/11/2007 10:30 HKG A 0200002 23/11/2007 09:30 HKG D
0200002 12/11/2007 10:30 HKG A 0200002 24/11/2007 10:30 TAI D
0200002 13/11/2007 10:30 HKG A Null Null Null Null Null
0200002 14/11/2007 10:30 HKG A Null Null Null Null Null
The above result actually means that TableA and TableB are joined horizontally. How can I do this?Can anyone help me? Thank you!!!
-
DECLARE @TableA TABLE (rowid int IDENTITY(1,1),portid ...)
INSERT INTO @TableA (portid,acid,adDate,adTime,adPort,adType)
SELECT portid,acid,adDate,adTime,adPort,adType FROM [TableA] ORDER BY portid ASC
DECLARE @TableB TABLE (rowid int IDENTITY(1,1),portid ...
INSERT INTO @TableB (portid,acid,adDate,adTime,adPort,adType)
SELECT portid,acid,adDate,adTime,adPort,adType FROM [TableB] ORDER BY portid ASC
SELECT a.portid,a.acid,a.adDate,a.adTime,a.adPort,a.adType,b.portid,b.acid,b.adDate,b.adTime,b.adPort,b.adType
FROM @TableA a
FULL JOIN @TableB b ON b.rowid = a.rowid
ORDER BY COALESCE(a.rowid,b.rowid)
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David, I think the result of the query you have written is not my expected result.
Suppose there are two tables:
Table A: Table B:
id value id value
1 A 4 D
2 B 5 E
3 C
My expected result is:
Result:
idA valueA idB valueB
1 A 4 D
2 B 5 E
3 C
And your result is:
idA valueA idB valueB
1 A Null Null
2 B Null Null
3 C Null Null
Null Null 4 D
Null Null 5 E
-
It works for me
Are you sure you are creating and using the two temp tables and inserting your current data into them ?
Far away is close at hand in the images of elsewhere.
Anon. -
Why you expect this result:
idA valueA idB valueB
1 A 4 D
2 B 5 E
3 C
Why not
idA valueA idB valueB
1 A 5 E
2 B 4 D
3 C
?
Why not
idA valueA idB valueB
1 A
2 B 5 E
3 C 4 D
?
What in you tables defines which lines joins which one from another table?
_____________
Code for TallyGenerator -
Well, In fact, The above is just a sample that is similar to my case. Actually my case is:
I have the table like this:
ArrDep
id pid adDate adTime adPort adType
1 020004 10/11/2007 11:00 HKG A
2 020004 12/11/2007 14:00 ZHA A
3 020004 14/11/2007 16:00 MAC D
4 020004 11/11/2007 17:00 TAI D
My expected result is:
id pid adDateA adTimeA adPortA adTypeA adDateB adTimeB adPortB adTypeB
1 020004 10/11/2007 11:00 HKG A 11/11/2007 17:00 MAC D
2 020004 12/11/2007 14:00 ZHA A 14/11/2007 16:00 TAI D
Explanation:
pid stands for person id. And adType stands for the date type such like Arrival Date and Departure Date.
The orginally table ArrDep should be separated into two parts:
select * from ArrDep Where AdType = 'A' //get all arrival records
select * from ArrDep Where AdType = 'D' //get all departure records
Then sort the above result by adDate. And join these two results into this:
id pid adDateA adTimeA adPortA adTypeA adDateB adTimeB adPortB adTypeB
1 020004 10/11/2007 11:00 HKG A 11/11/2007 17:00 MAC D
2 020004 12/11/2007 14:00 ZHA A 14/11/2007 16:00 TAI D
-
Your example does not make much sense to me.
Why are you trying to join departure to the first arrival, not to the last arrival before departure?
Your expected result shows that the person arrived to HKG and was departed from there to MAC despite the recorded fact that this person was in ZHA in between those events.
_____________
Code for TallyGenerator -
Sorry for misunderstanding you. I have edited my post. See if you understand my question. Because there are so many situations happened in my case. Sometimes, the total number of arrival records may be greater than the departure records. Sometimes, the total number of departure records are greater than arrivals'. And sometimes the total number of arrival and departure records are same.
-
So, you need to join every Arrival record to the next record (by time recorded) if it's Departure.
And you need to join every Departure record to previous record (by time recorded) if it's Arrival.
If the next record after Arrival is another Arrival it should not be joined.
If the previous record before Departure is another Departure it should not be joined.
Is it right?
_____________
Code for TallyGenerator -
exactly yes.
-
So, 1st select consequent pairs of events.
Sorry, I'll not gonna use separated date-time values. Fix your table by combining them into single datetime column by yourself.
SELECT E1.pid, E1.EventTime as ArrivalTime, MIN(E2.EventTime) as DepartureTime
FROM dbo.ArrDep E1
INNER JOIN dbo.ArrDep E2 ON E1.pid = E2.pid and E1.EventTime < E2.EventTime
GROUP BY E1.pid, E1.EventTime
Now, when we have all pairs of events, we need to filter them out by joining to Arrivals and Departures:
Select A.*, D.*
FROM ({Query above}) P
LEFT JOIN dbo.ArrDep A ON P.pid = A.pid AND P.ArrivalTime = A.EventTime AND A.adType = 'A'
LEFT JOIN dbo.ArrDep D ON P.pid = D.pid AND P.DepartureTime = D.EventTime AND D.adType = 'D'
Was easy so far?
That's actually it.
The only thing you probably want to add is
WHERE (A.pid IS NOT NULL OR D.pid IS NOT NULL)
This will make sure that at least either Arrival or Departure is presented. Just to eliminate "All NULLs" rows.
And If you want to return passengers having single Arrival record you need to replace INNER JOIN with LEFT JOIN in the first query.
Give it a try on your actual data.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply