x

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

more ▼

asked Jan 20 '10 at 03:57 PM in Default

desiabhi gravatar image

desiabhi
33 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jan 20 '10 at 06:02 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

Thanks so very much, the solution is very accurate.
Jan 20 '10 at 06:30 PM desiabhi
Kev, do you prefer ISNULL to COALESCE? If so, for what reasons? I am curious as I have always used ISNULL (and NZ) in programming but had to make do with COALESCE in TSQL...
Jan 21 '10 at 12:12 PM Fatherjack ♦♦
Fatherjack: I don't think it's a question of preference, just habit! If I only have one expression to test, then isnull is what I use, rather than COALESCE (expr, constant). In this case I don't see any gain or loss for using either.
Jan 21 '10 at 01:25 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x1816
x977
x402

asked: Jan 20 '10 at 03:57 PM

Seen: 1316 times

Last Updated: Jan 20 '10 at 04:55 PM