query to eliminate the many to many related data

Question

Post reply

query to eliminate the many to many related data

LoveSQL

Old Hand

Points: 397
More actions
February 25, 2009 at 6:28 pm

#197548

Hi Every one,
I would apprecriate helping me out on a query that will result in one to one related IDs from a many to many realated data table.
I have a table UserRole(UserID,RoleID) that has the data like this
UserID RoleID
11
45
35
22
23
34
I want to list the IDs that has only one to one relationship, for example in this case I should filter out all users that has many roles and all roles that are associated with many users. so that the query results should be 1,1
I hope I make some sense. Please let me know.
Thanks..

rb

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think it should be fairly simple, right? didn't test it, but this is my first guess:

SELECT

UserRole.UserID,

UserRole.RoleID

FROM UserRole

LEFT OUTER JOIN (SELECT UserId, COUNT(RoleID) AS CNT

FROM UserRole

GROUP BY UserID) MYALIAS ON UserRole.UserId = MYALIAS.UserID

WHERE MYALIAS.CNT =1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LoveSQL Old Hand Points: 397 More actions · Answer 2

Hi Lowel,

Thanks for the quick response.

I think you are very close,

You query resulting two recods

11

45

Role 5 is associated to user 4 so it should be eliminated

Any Idea?

rb

LoveSQL Old Hand Points: 397 More actions · Answer 3

I think I figured out from your hint

Following modification is working

Please let me know if I am wrong --thanks

SELECT

UserRole.UserID,

UserRole.RoleID

FROM UserRole

LEFT OUTER JOIN (SELECT UserId, COUNT(RoleID) AS CNT

FROM UserRole

GROUP BY UserID) MYALIAS ON UserRole.UserId = MYALIAS.UserID

LEFT JOIN (SELECT RoleID, COUNT(UserId) AS CNT2

FROM UserRole

GROUP BY RoleID) MYALIAS2 ON UserRole.RoleID = MYALIAS2.RoleID

WHERE MYALIAS.CNT =1

AND MYALIAS2.CNT2 =1

rb

Lowell SSC Guru Points: 323518 More actions · Answer 4

yep i think you got it;

do you also need to find users with zero roles, or roles with zero users?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!