Help with SQL query - TSQL

Transact-SQL
1

Hi All,

New user so standing by for a barrage of complaints about not formatting my message properly! I'll get there.

Let's say I have a table CONTACT [ID, Type, FirstName, LastName]

and a table HOUSEHOLD [Ref, HouseholdID, ChildID]

CONTACT is used to store clients, but you can group them by household

So ID=100 Bernard Smith and ID=102 Sharon Smith might be represented in HOUSEHOLD as

HOUSEHOLD
Ref HouseholdID ChildID
1 123 100
2 123 102

Furthermore, the household I have set up is itself a contact and appears as ID 123 in CONTACT

CONTACT
ID Type FirstName LastName
5 Individual Graham Snoring
100 Individual Bernard Smith
102 Individual Sharon Smith
123 Household [null] Smith

I need a query that extracts all my contacts but suppresses Individuals who are part of a household in favour of just the household record. So the perfect result from the data in the above tables would be

QUERY RESULT
ID Type FirstName LastName
5 Individual Graham Snoring
123 Household [null] Smith

Can someone guide me through this please?

Thanks

Tom

2 
SELECT C.*
FROM CONTACT C
WHERE ID NOT IN (
    SELECT H.ChildID
    FROM HOUSEHOLD H
    )
3

Btw, it is considered "nicer" to provide data in directly usable table form, like this:


CREATE TABLE #HOUSEHOLD ( Ref int not null, HouseholdID int not null, ChildID int not null )
insert into #HOUSEHOLD values
    (1, 123, 100),
    (2, 123, 102)

CREATE TABLE #CONTACT ( ID int not null, Type varchar(30) not null, FirstName varchar(50) null, LastName varchar(100) not null )
insert into #CONTACT values
    (5, 'Individual', 'Graham', 'Snoring'),
    (100, 'Individual', 'Bernard', 'Smith'),
    (102, 'Individual', 'Sharon', 'Smith'),
    (123, 'Household', null, 'Smith')
4

hi hope this helps

please click arrow mark to the left for script 
CREATE TABLE #HOUSEHOLD ( Ref int not null, HouseholdID int not null, ChildID int not null )
insert into #HOUSEHOLD values
    (1, 123, 100),
    (2, 123, 102)

CREATE TABLE #CONTACT ( ID int not null, Type varchar(30) not null, FirstName varchar(50) null, LastName varchar(100) not null )
insert into #CONTACT values
    (5, 'Individual', 'Graham', 'Snoring'),
    (100, 'Individual', 'Bernard', 'Smith'),
    (102, 'Individual', 'Sharon', 'Smith'),
    (123, 'Household', null, 'Smith')


select 'HouseHold',* from #HOUSEHOLD
select 'Contact',* from #CONTACT
----------------------------------------------------------------
-- Actual Query 

select 
        'SQL Query'
         ,a.ID
		 ,a.Type
		 ,a.FirstName
		 ,a.LastName
from 
   #CONTACT a left outer join #HOUSEHOLD b on a.ID = b.ChildID
where 
   b.Ref is null
----------------------------------------------------------------

drop table #HOUSEHOLD
drop table #CONTACT

image