Omit double data from view - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Omit double data from view

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources