Self Join help please!!

[NoelMG]

The database keeps a record of people attending a Gala Dinner and is intended to show who is co-sponsoring a table with whom.

For instance - Joe Bloggs could be co-sponsoring a table with John Smith. The user selects the person the delegate will be sharing with from a combo box which places the corresponding delegate's ID into the CoSponsor field.

The query I have uses a self join between the CoSponsor and the ID field to show for instance:
Joe Bloggs - co-sponsoring table with John Smith.

The trouble is, the query will then also show another line for

John Smith - co-sponsoring table with Joe Bloggs.

Is there any way to only show this table once in the query?

Thanks in advance for any help you can offer!!

Thanks,
Noel.

 

[amorous]

Please show us the code for your query.

-VJ

 

[NoelMG]

The code I'm using so far is:

SELECT tblPeople.[People ID], tblPeople_1.[People ID], tblPeople.CoSponsor, tblPeople.[Known as], tblPeople.[Surname Known As], tblOrganisation.[Organisation name] AS [Company name], tblPeople.DinnerTableType, IIf([tblpeople_1].[known as] Is Not Null,"Co-Sponsor with") AS CoSponsorWith, tblPeople_1.[Known as], tblPeople_1.[Surname Known As], tblOrganisation_1.[Organisation name] AS [1company name], tblPeople_1.DinnerTableType
FROM (tblOrganisation RIGHT JOIN (tblPeople LEFT JOIN tblPeople AS tblPeople_1 ON tblPeople.CoSponsor = tblPeople_1.[People ID]) ON tblOrganisation.[Organisation ID] = tblPeople.[Organisation ID]) LEFT JOIN tblOrganisation AS tblOrganisation_1 ON tblPeople_1.[Organisation ID] = tblOrganisation_1.[Organisation ID];

 

[lespaul]

Looks like you have two tables here:

tblPeople
PeopleID
CoSponsor
[Known as]
[Surname Known As]
DinnerTableType

tblOrganisation
[Organisation ID]
[Organisation Name]

Do you have other tables that have information about the date of the event, how many tables, do you identify the tables in some way?

The "right" answer is that it doesn't seem like your design is normalized. The first hint to that is: an entity (or table) should only have information about the PK of that table. You have tblPeople, which indicates it is a table that stores information about people. What does DinnerTableType have to do with a person? It has to do with the TABLE not the PERSON. That field should be in tblTables.

I would expect a normalized database to be set up similarly to this:

tblPeople
PeopleID
Name
Address


tblTables
TableID
CoSponsored (logical Y/N T/F)

then another table with

tblSponsors
SponsorID (autonumber PK just because that's the way I like it!)
TableID (FK to tblTables)
PersonID (FK to tblPeople)

Then you can very easily run a query that will do what you want.

For more information, check out 'The Fundamentals of Relational Database Design'



Leslie