April 5, 2011 at 1:34 pm
using sql server 2005
given the following:
declare @contact_type table(contactTypeID int,contactType varchar(50))
declare @resident table(residentID int,firstName varchar(50), lastName varchar(50))
declare @resident_contact table(residentContactID int,contactName varchar(50),residentID int,contactTypeID int)
insert into @contact_type (contactTypeID, contactType)
values(1,'Emergency')
,(2,'Spouse')
insert into @resident(residentID,firstName,lastName)
values(1,'Peter','Parker')
,(2,'Jimi','Hendrix')
insert into @resident_contact(residentContactID,contactName,residentID,contactTypeID)
values(1,'Aunt Mae',1,1)
,(2,'Mary Jane',1,2)
,(3,'James Hendrix Sr.',2,1)
how do I get a list of residents and the contactTypes they do not have without the use of a curser.
I want to end up with the following because Jimi has no spouse
Jimi Hendrix, Spouse
April 5, 2011 at 2:14 pm
A co-worker helped with this. His solution
declare @contact_type table(contactTypeID int,contactType varchar(50))
declare @resident table(residentID int,firstName varchar(50), lastName varchar(50))
declare @resident_contact table(residentContactID int,contactName varchar(50),residentID int,contactTypeID int)
insert into @contact_type (contactTypeID, contactType)
values(1,'Emergency')
,(2,'Spouse')
insert into @resident(residentID,firstName,lastName)
values (1,'Peter','Parker')
,(2,'Jimi','Hendrix')
insert into @resident_contact(residentContactID,contactName,residentID,contactTypeID)
values(1,'Aunt Mae',1,1)
,(2,'Mary Jane',1,2)
,(3,'James Hendrix Sr.',2,1)
SELECT RS_SEED.ResidentID
,RS_SEED.firstName
,RS_SEED.lastName
,RS_SEED.contactType
,RS_SEED.contactTypeID
,ResidentConact.residentContactID
,ResidentConact.contactName
FROM ( SELECT DISTINCT
TOP 100 PERCENT
resident.ResidentID
,resident.firstName
,resident.lastName
,contact_type.contactType
,contact_type.contactTypeID
FROM @resident resident
CROSS JOIN @contact_type contact_type
ORDER BY resident.ResidentID
,contact_type.contactTypeID
) RS_SEED
LEFT JOIN @resident_contact ResidentConact
ON RS_SEED.contactTypeID = ResidentConact.contactTypeID
AND RS_SEED.residentID = ResidentConact.residentID
WHERE ResidentConact.contactTypeID IS NULL
ORDER BY RS_SEED.lastName
,RS_SEED.firstName
,ResidentConact.contactName
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply