Hi all,

As I am relearning SQL, I came upon a problem that anyone here could probably answer in their sleep. If someone could give me some help, it would be very appreciated!

I am working with three tables, Person, Affiliation, and Entity.

Person has data on people.
Entity has data on entities (like departments or groups) of which people are a part.
Affiliation is how the Person and Entity tables are linked - the Affiliation table contains rows with a PersonID and EntityID. So if Person 5 is part of Entity 6, there will be a record in the Affiliation table corresponding to that.

I want to SELECT a list all people as well as the Entities with which they are affiliated.

I tried doing this by inner joining Entity and Affiliation on Affiliation.EntityID = Entity.EntityID, and then outer joining Person.PersonID *= Affiliation.PersonID to the results (since I want all people returned, even if they are not affiliated with any entities). But when I try this, I get the error

The table 'Affiliation' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Ok, I realize now that you can't have tables in an inner and outer join in the same query. But is there some solution that will allow me to pull this data in the way I want, without creating a temporary table?

Thanks in advance for the help!

Oops, I think I figured out a solution - using outer joins to join Affiliation to Entity and Person.

Seems amazingly obvious. This is what happens when you try working on a database after midnight - obvious things take you hours to realize.

Josh