Ideas for displaying data from 1 main table and 4 related tables

[millerk]

The simplest way to explain would be to give an example.
Say I have a set of database tables like this:
Person(SSN, DOB)
Alias(SSN, FirstName, LastName)
Address(SSN, Street, City, State)
Phone(SSN, PhoneNum)
Skill(SSN, SkillDescription)
There's one person with a particular SSN and that person can have many aliases, addresses, phone numbers, and skills.

Then I have a form that allows users to search for people. For instance, find all the people in the database that live in Chicago. Currently I have a query that ends up looking like this:
Select * from
Person Pe
left join Alias Al on Pe.SSN = Al.SSN
left join Address Ad on Pe.SSN = Ad.SSN
left join Phone Ph on Pe.SSN = Ph.SSN
left join Skills Sk on Pe.SSN = Sk.SSN
where Pe.SSN in
(select SSN from Address where City = 'Chicago')

And I'm basically just displaying the results of this query on my form in a datagrid. This works and gives the user all the data they need, but it's confusing because the result of the joins can produce a lot of records if the people that are found have multiple records in each of the other tables. As a simple example, if 2 people are found and they each have 2 aliases, 2 addresses, 2 phone numbers, and 5 skills, the user will see 80 records.

I guess this is a general question on displaying data with many to one relationships. I'm not sure how to go about displaying this kind of data. Clearly, a simple datagrid isn't the best way, but I'm not sure how else to do it.

 

[ca8msm]

Why not return just one record for each person with a count of how many aliases, addresses, phone numbers and skills that they have? The user could then click into each individual record to see all of the details for that person.

Also, if you stick with your current method, you may want to drop the "In" clause and simply look for "where City = 'Chicago'".


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[millerk]

The problem with the first suggestion is that the information listed in the Person table is relatively meaningless without the data from the related tables. So the user would then have to click on every person and get the related data to know if this is the person they are looking for. And if they are going to have to look at the data for every person, it's just as easy to show all the data at once.

Removing the IN subquery and just using where conditions is actually what I started with. The problem with that is that you don't get all the data. For instance, say there's a guy named John Smith who has addresses listed in Chicago, New York, and Los Angeles. If I do a search for Chicago, I only see the records that have Chicago in them. What I really want to see is all the info for the people who have an address in Chicago. So I want the user to see that John Smith not only has an address in Chicago, but also in New York and Los Angeles.
That's why I switched to using the subquery. That way I first get all the SSN's of the people who match a search, then I get all the data for all those people.

 

[ca8msm]

The problem with the first suggestion is that the information listed in the Person table is relatively meaningless without the data from the related tables. So the user would then have to click on every person and get the related data to know if this is the person they are looking for. And if they are going to have to look at the data for every person, it's just as easy to show all the data at once.

You can either show the data that you think is meaningful (i.e. my example but maybe with the name of the person) and group (or count) the other data or you can show it all (as you are doing now). You can't have it both ways and show all data but not all the records.

So I want the user to see that John Smith not only has an address in Chicago, but also in New York and Los Angeles.

OK, I didn't know this was what you wanted to do (you just said "find all the people in the database that live in Chicago" which is what my example would have done).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[millerk]

Here's an example that might make things more clear.
Take the following two people:
1. This person(SSN 111-11-1111) has 2 records in the alias table(James Smith and Jimmy Smith) and 2 records in the Address table(Chicago and New York)
2. This person(SSN-555-55-5555) also has 2 records in the alias table(William Jonston and Bill Johnston) and 3 records in the address table(Chicago, Seattle, San Francisco)
If I do a search for people with an address in Chicago, my current displayed results look like:
SSN FName LName City
---------------------------------
111-11-1111 James Smith Chicago
111-11-1111 James Smith New York
111-11-1111 Jimmy Smith Chicago
111-11-1111 Jimmy Smith New York
555-55-5555 William Jonston Chicago
555-55-5555 William Jonston Seattle
555-55-5555 William Jonston San Francisco
555-55-5555 Bill Johnston Chicago
555-55-5555 Bill Johnston Seattle
555-55-5555 Bill Johnston New York

What I'd really like to display is something more like:
Example 1
111-11-1111 James Smith Chicago
Jimmy Smith New York
555-55-5555 William Jonston Chicago
Bill Johnston Seattle
New York

or maybe something like:
Example 2
111-11-1111
James Smith, Jimmy Smith
Chicago, New York
555-55-5555
William Jonston, Bill Johnston
Chicago, Seattle, New York

I'm not sure exactly what I want it to look like. I just want to display it in a way that doesn't have so much repeated data like the single query/simple datagrid method I'm currently using. I've been programming(VB/SQL) for quite a few years, but I'm just started doing web development. So my first thought was to manipulate the sql to make the resultset look like my Example 1 above. But I'm not sure how to do that and it's probably not the best idea anyway.
Any suggestions are greatly appreciated.

Thanks

 

[ca8msm]

OK, I think both example 1 and example 2 can be acheived by using what's known as "nested" controls. Example 1 could quite easily be done by using nested DataGrids (or GridViews) but example 2 would be more suited to nested Repeaters.

Here's some articles for both scenarios that should get you on your way:

http://www.standardio.org/article.aspx?id=155

http://www.codeguru.com/Csharp/.NET/net_data/datagrid/article.php/c5619

http://support.microsoft.com/kb/306154/EN-US/

http://www.codeproject.com/aspnet/AspNetNestedRepeaters.asp

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[millerk]

Thanks.
I'll take a look at those.