Complete the following problem using basic SQL commands.  Basic SQL
means not specifically tied to TSQL, SQL PL or DB 2 Versions.
if I am given a table with the addresses of consumers to whom we wish
to send junk mail.  The table has a fam(family) column that links
Consumers with the same street address.  We need this because our
rules are that we mail only one offer to a household.  The field
contains the PK value of the Consumers record of the first person who
had this address, thus:
Consumers
Name		address			id		fam
 Bob		A			1		Null
 Joe		B			3		Null
 Mark		C			5		Null
Mary		A			2		1
Vickie		B			4		3
Wayne		D			6		null

We need to delete those rows where fam is null but there are other
family members on the mailing list.  How would you do it in SQL?

---

Request for Question Clarification by twitch-ga on 27 Sep 2002 10:23 PDT

fahd-

> We need to delete those rows where fam is null but there are other
> family members on the mailing list.

I'm not quite certain what you mean. If the FAM field is NULL, how is
it possible to know whether or not other members of the Family are on
the list? Is it perhaps that you need to delete records that have NULL
FAM field, but have a Consumer_ID field equal to a value that other
records use in the FAM field?

---

Clarification of Question by fahd-ga on 27 Sep 2002 21:47 PDT

I have not done this before so please help me understand what is meant by:
We need to delete those rows where fam is null but there are other
family members on the mailing list.  How would you do it in SQL?

I'm not sure why you would want to "delete those rows where fam is
null but there are other family members on the mailing list." but his
should do what you asked:

delete 
  from consumers c1
 where c1.fam is null
   and exists (select c2.fam
                 from consumers c2
                where c1.id = c2.fam);