Matching corresponding records from two or more tables
I have table called INTAKE with following data.
IntakeID Dateintake AnimalID
1 2009-02-05 001
2 2009-06-19 002
3 2009-07-06 003
4 2009-07-12 004
5 2009-07-15 005
6 2009-08-04 003
7 2009-08-04 004
8 2009-08-14 003
9 2009-12-12 004
I have License table which will show the license dates purched for the corresponding AnimalID
LicenseID AnimalID DatePurchased
2009-01 004 2009-08-09
2009-02 003 2009-08-12
2009-03 004 2009-12-16
I need to show the results as follows:
AnimalID LicenseID DatePurchesed
001 - -
002 - -
003 2009-02 2009-08-12
004 2009-01 2009-08-09
004 2009-03 2009-12-16
Thanks for your help
Desiabhi
Best Answer
Try this
declare @intake table (IntakeID int, Dateintake datetime, AnimalID varchar(10))
insert into @intake
select 1,'2009-02-05','001'
union select 2,'2009-06-19','002'
union select 3,'2009-07-06','003'
union select 4,'2009-07-12','004'
union select 5,'2009-07-15','005'
union select 6,'2009-08-04','003'
union select 7,'2009-08-04','004'
union select 8,'2009-08-14','003'
union select 9,'2009-12-12','004'
declare @License table
(LicenseID varchar(10), AnimalID varchar(10), DatePurchased datetime)
insert into @License
select '2009-01','004','2009-08-09'
union select '2009-02','003','2009-08-12'
union select '2009-03','004','2009-12-16'
select
uniqueAnimals.AnimalID,
isnull(License.LicenseID, ' - ') as LicenseID,
isnull(replace(convert(varchar, License.DatePurchased, 111),'/','-'),' - ') as DatePurchased
from
(
select distinct AnimalID from @intake
) uniqueAnimals
left join
@License License on License.AnimalID = uniqueAnimals.AnimalID
this gives
AnimalID LicenseID DatePurchased
---------- ---------- -------------
001 - -
002 - -
003 2009-02 2009-08-12
004 2009-01 2009-08-09
004 2009-03 2009-12-16
005 - -
Basically what this is doing is joining the unique list of AnimalIDs from the intake table, to the license table using a left join to get the null values where no license exists. The isnull, replace and convert bits are just to make the output look as you specified, but if that's not needed then adjust to suit.
