November 12, 2009 at 9:40 am
My table and data as follow,
declare @tTrip TABLE (
TID int not null primary key,
DrID1 varchar (20) NULL,
DrID2 varchar (20) NULL,
DrID3 varchar (20) NULL
)
insert into @tTrip values('1','drv06',null,null)
insert into @tTrip values('2','drv08','drv11',null)
declare @tDrv TABLE (
DrID varchar(20) not null primary key,
DNme varchar (20) not NULL
)
insert into @tDrv values('drv02','James SN')
insert into @tDrv values('drv03','Karel')
insert into @tDrv values('drv06','Benjamin')
insert into @tDrv values('drv07','Sam')
insert into @tDrv values('drv08','Mercury')
insert into @tDrv values('drv11','Jupiter')
I want to query, and the resultset as follow,
TID | DrID1 | DNme | DrID2 | DNme | DrID3 | DNme
---------------------------------------------------------------
1 | drv06 | Benjamin | null | null | null | null
2 | drv08 | Mercury | drv11 | Jupiter | null | null
How my SQL look's like?
November 12, 2009 at 10:04 am
One thing up front:
Your table definition for @tTrip is not normalized.
I strongly recommend to consider changing the structure to have only one DrID column and myabe an addtl. column for DrID number.
This would give you the flexibility to add as many drivers as you like and you wouldn't have to store all those NULL values.
it's going to cause pain in the long run.
wait a minute. It already happened...
To get the result you asked for (and under the assumption of a normalized table) you could use the CrossTab concept as described in the link in my signature. You even could do Dynamic Cross Tabs to have a flexible number of DrIDs. And all of that stuff without touching your (then) normalized table structure if number of DrIDs per TID will change...
Now to your original question:
Since you have the DrID in three columns you need to do three joins:
SELECT
TID,
DRID1,
Drv1.DrID DrID1,
Drv1.Dnme,
Drv2.DrID DrID2,
Drv2.Dnme,
Drv3.DrID DrID3,
Drv3.Dnme
FROM @tTrip Trip
LEFT OUTER JOIN @tDrv Drv1
ON Trip.DrId1 = Drv1.DrID
LEFT OUTER JOIN @tDrv Drv2
ON Trip.DrId2 = Drv2.DrID
LEFT OUTER JOIN @tDrv Drv3
ON Trip.DrId3 = Drv3.DrID
November 12, 2009 at 10:15 am
How about showing us what you have written so far? This way we can see what you are trying to do and provide you with some constructive feedback.
November 12, 2009 at 10:48 am
tq very much sir lmu92
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply