Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DennisMollet
Starting Member
4 Posts |
 Posted - 2010-08-07 : 11:53:33
|
I have a view that returns data from multiple tables and looks like the following:
Name / Type / Value
Joe / A / 10
Joe / B / 5
John / A / 3
Jeff / Null / Null
I need to return one record for every name. When a name pulls up multiple times, I want to be able to tell SQL which name to pull in based upon "Type." For instance, if I want Type = A, I would like the database to give me "Joe/A/10", "John/A/3", "Jeff/Null/Null". If I want Type = B, the database should return "Joe/B/5", "John/A/3", "Jeff/Null/Null"
There is a potentially unlimited number of types. Anyone who has no type will show up like Jeff - "Jeff/Null/Null"
It's like I want to tell SQL, "Give me the first name where Type = A, but if Type is never A, just give me the name where there's any Type"
I have a way to split the view up so that I have only one type in the view.
for instance:
View 1
Name / Type / Value
Joe / A / 10
John / A / 3
View 2
Name / Type / Value
Joe / B / 5
View 3
Name / Type / Value
Jeff / Null / Null
If there was a way to take these multiple views and Union them (if that's the right word), that might work.
Thanks!
Dennis |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-08-07 : 12:41:04
|
| Please post DDL schema of your tables (not views) and some sample data. and of course your desired output. |
 |
|
|
DennisMollet
Starting Member
4 Posts |
Posted - 2010-08-07 : 12:55:59
|
I don't know what DDL Schema means, but here's what my tables look like:
People Table:
PersonID- guid primary key field
Name - varchar
etc.
PeopleInMembershipDetails Table:
ID - guid primary key field
PersonID - guid foreign key field
MembershipDetailID - guid foreign key field
MembershipDetails Table:
MembershipDetailID - guid primary key field
MembershipID - guid foreign key field
Name - varchar
etc.
Memberships Table:
MembershipID - guid primary key field
Name - varchar
etc.
People -> PeopleInMembershipDetails One to Many relationship
MembershipDetails -> PeopleInMembershipdetails One to Many relationship
Memberships - > MembershipDetails One to Many relationship
-In the database, there can be many "Memberships" (School, Business, Church, etc.)
-Each Membership can have many "MembershipDetails" (Redwood High School, University of Somewhere, etc.)
-Each person in the database can belong to one MembershipDetail for each Membership (Joe can be in one school, one business, and one church) - This is accomplished through business logic, not limited by the DB
-A person does not have to belong to any MembershipDetails
I need to be able to see all people, but I only want to see them once. If a person is a member of a "School", I want to see the school. If they are not in a "School", then I want to see the person with "Null" under the MembershipDetailName column.
Does that help?
Dennis |
|
|
|
DennisMollet
Starting Member
4 Posts |
Posted - 2010-08-07 : 13:37:35
|
Alternatively, a way to create a view that looks like this would work:
PersonName / MembershipDetailName1 / MembershipDetailName2 / etc.
Joe / School1 / Church1 / etc.
Jeff / School1 / Church2 / etc.
etc.
The key is that I need to see every name just one time, and I need to see at least the one MembershipDetail. If I can see more, that's fine.
Thanks,
Dennis |
|
|
|
|
|
|
|
|
Omit double data from view