combining rows that match certain criteria

gfrank

I have two tables...

one (PEOPLE) holds the first and last names of wives and husbands. The other (ADDRESSES) holds addresses.

the PEOPLE table has fields which hold an id number to an address and they also hold id numbers matching the individules to their spouse. For instance:

PEOPLE table

peopleID || Fname || Lname || Add_code || Spouse_ID

1 Tom Jones 1 2
2 Jill Jones 1 1
3 Gary Thomas 2 4
4 Julie Thomas 2 3
5 John Smith 3 6
6 Lori Smith 3 5

ADDRESS table

adID || Addr || City || State

1 123 main Sacramento CA
2 234 Lincoln Ave Phoneix AZ
3 454 Euclid Seattle WA

I want to pull data for a mailing list... but I only want to send one copy to each address, not each person...

so I want a list like:

Tom & Jill Jones 123 main Sacramento, CA
Gary & Julie Thomas 234 Lincoln Ave Phoneix, AZ

John & Lori Smith 454 Euclid Seattle, WA

I used this query...

SELECT Fname, Lname, Addr, City, State FROM ADDRESS INNER JOIN PEOPLE ON adID = Add_code

but what that returns is seach name matched to each address...
Tom Jones 123 main Sacramento, CA
Jill Jones 123 main Sacramento, CA
Gary Thomas 234 Lincoln Ave Phoneix, AZ
Julie Thomas 234 Lincoln Ave Phoneix, AZ
John Smith 454 Euclid Seattle, WA
Lori Smith 454 Euclid Seattle, WA

How can I change the Query so that it will return only one row for each address but get the first name (and last name in case it is different) for both spouses that match the address?

(I know I could do it in PHP after pulling the data out... but I would like to do it all in the SQL statement if possible)

Would i need to make a temporary table. or sub query to match the spouses first and them join to the addresses? how woudl I do that if that is the answer?

Thanks!

konsu

what do you want the query to do if more than two people live at the same address?

gfrank

Typically there will only be one or two people per address match...
but if for some reason there are more then I guess they would be included with the one address.

or I guess if the "spouse ID" did not match... lets say a couple... then one single name at the same address... I guess the couple with matching spouse ids would be one address and the other a separate address line.

But I doubt that will ever or will rarely happen because the address entry is matched to married couples... I would guess that if there were two couples at the same address they would have identical address entries but two address ids... so it would not matter.

konsu

an ugly way to do it is to join the table people on to itself on add_code and non-identical peopleid. this will match rows in people table that have the same address but are different persons. note that if there are more than two people living at the same address you will get more than one row with the same add_code. which is why i say it is ugly. i would proceed from here, i am sure there is a better way to do it. perhaps using sub-queries?