Thread: SQL Left Outer Join Problem

I want to create a table of siblings from the following table:


Child, Surname, Address, Firstname
1, Adamson, 1 The Beeches, Joe
2, Adamson, 1 The Beeches, Mike
3, Bagshott, 2 The Beeches, Phil
4, Bagshott, 15 The Beeches, April
5, Adamson, 8A Acacia Avenue, Phil
6, Meldrew, The Manor House, Alfred
7, Adamson, 8A Acacia Avenue, Peter
8, Meldrew, The Manor House, Quentin
9, Adamson, 1 The Beeches, Susan

This is the desired result:


Family, Surname, Address, Siblings
1, Adamson, 1 The Beeches, Joe, Mike & Susan
2, Bagshott, 2 The Beeches, Phil
3, Bagshott, 15 The Beeches, April
4, Adamson, 8A Acacia Avenue, Phil & Peter
5, Meldrew, The Manor House, Alfred & Quentin

So far I have worked out that I need to create a table of unique Family addresses, but cannot get my head around how to do the rest ... Any help would be much appreciated.

This seems to be more of a database design question, so I will move this thread to that forum.

So it looks like you want to relate families to children to addresses...so something like this:

Families
FamilyID Surname

Siblings
SiblingID FirstName

Addresses
AddressID Address

Families_Addresses_Link
FamilyID AddressID

Families_Siblings_Link
FamilyID SiblingID

Does that make sense?