Help with SQL join issue (not sure how to describe it)

[millerk]

OK, here's the issue.
Say I have 3 tables (tblEntity, tblEntityName, tblEntityAddress)
An entity represents a person. That person can have multiple names(aliases) and multiple addresses. So there is a one-to-many relationship between Entity and EntityName and a one-to-many relationship between Entity and EntityAddress. The primary key for Entity is EntityId.
So I do something like this:
Select ...
From tblEntity E
left join tblEntityName EN on E.EntityId = EN.EntityID
left join tblEntityAddress EA on E.EntityId = EA.EntityID
where E.EntityID = <some number>

This gives me all the data I need, but in the case of a person with 3 different aliases and 3 different addresses, I get 9 results back. I know this makes sense because each alias combined with each address is a unique record in the resultset. I'm currently just displaying the results of this query in a grid, but displaying the results this way is confusing to the user. What I really want to show them is that there are 3 aliases and 3 addresses associated with this particular person.

Ideally, I'd like to just combine the results. Something like a Union, but combining columns instead of rows.

Any ideas ?

 

[millerk]

Just to clarify, here is a simplified example of what I'm currently getting:
Name Address
--------------
Jim Chicago
Jim Los Angeles
Jim New York
James Chicago
James Los Angeles
James New York
Jimmy Chicago
Jimmy Los Angeles
Jimmy New York

And what I'd like to display:
Name Address
---------------
Jim Chicago
James New York
Jimmy Los Angeles

Although that doesn't really make sense either because it implies that each of those aliases is associated with the city in the same row, and it's really not.

 

[plevey]

Interesting! Let me get this staight...

Entity 001 has Names

001 - 01 Jim
001 - 02 Jimmy
001 - 03 James

and Addresses

001 - 01 Chicao
001 - 02 New York
001 - 03 Los Angles

How do you determine that Jim is associated with Chicago,
James with New York and Jimmy with Los Angeles unless each alias is linked to the Address ?




If fishing was easy it would be called catching

 

[millerk]

There is no direct relationship between one name and one address.
James, Jim, and Jimmy all refer to one specific person who has listed addresses in Los Angeles, Chicago, and New York.

So the way the results come back does represent the correct relationship. It's just a bit confusing for the users. Plus, in my real case, there are actually 5 tables. One table that represents the Entity(person), and 4 other tables with many to one relationships to that person table. So if each of those other tables has 3 records related to a particular person, the resultset for that person is going to end up with 81 records.

Think of it this way:
Person(PersonID, DateOfBirth)
Address(PersonID, Street, City, State)
Name(PersonID, FirstName, LastName)
PhoneNumber(PersonID, PhoneNumber)
PastEmployers(PersonID, EmployerName)

So the question is how to best display all the information for a particular person without ending up with the number of rows equal to 1 * <number of addresses> * <num of names> * <num of PhoneNumbers> * <num of PastEmployers>

Maybe there isn't really a good way to do it in a grid using a single query.

 

[Gooser]

I've looked at this several different ways, and the easiest/clearest/simplest seems to be 5 selects.

Interesting problem, though.

v/r
Gooser

 

[plevey]

I can't see a way to get it all together in a single select.
What about using a stored procedure then use coalesce to get all the Addresses into a variable separated by commas?
Something like:
SELECT @AddessList = COALESCE(@AddressList + ', ', '') + CITY FROM Address

If fishing was easy it would be called catching