checking record existence in many-to-many r/ship

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 2000 Forums

SQL Server Development (2000)

checking record existence in many-to-many r/ship

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-16 : 09:46:32

murray writes "hey hey

had a good look through and i can't see this answered anywhere, so i thought i'd post it. :)

i've been trying to solve this for hours. i have a many-to-many
relationship between two entities, PEOPLE and GROUPS.
a person can be in many groups. a group can have many
people in it. there's a join table here to keep track of this.

here's the lay of the land:


------- PEOPLE TABLE -------
ID          NAME
1           john
2           alex
 
------- GROUP TABLE -------
ID          NAME
1           GROUPA
2           GROUPB
3           GROUPC
4           GROUPD
 
------- JOIN TABLE --------
PERSON_ID   GROUP_ID
1           1
1           2

what i want to know is which groups are NOT related to 'john' right now: ie.. GROUPC and GROUPD. also - can this query be done just JOINS instead of subqueries.

thanks!

murray"

ToddV
Posting Yak Master

218 Posts

Posted - 2003-05-16 : 10:04:23

How about this:

SELECT A.Name
FROM [Group Table] A
LEFT JOIN [JOIN TABLE] b
ON a.Group_ID = b.Group_ID
LEFT JOIN [PEOPLE TABLE] C
ON B.PERSON_ID = C.PERSON_ID AND
C.Name = 'john'
WHERE B.GroupID IS NULL

murray
Starting Member

2 Posts

Posted - 2003-05-17 : 00:19:47

hey there todd

thanks for the reply! i just tried it out on this set of data and there seems to be a problem. i have added another join to the join table, connecting the SECOND person with the THIRD group.

------- PEOPLE TABLE -------
ID NAME
1 john
2 alex

------- GROUP TABLE -------
ID NAME
1 GROUPA
2 GROUPB
3 GROUPC
4 GROUPD

------- JOIN TABLE --------
PERSON_ID GROUP_ID
1 1
1 2
2 3

the query result which should show GROUP C and GROUP D as groups unrelated to john now does NOT show GROUP C even though it has nothing to do with john.

any advice?

thanks in advance :)

murray

Edited by - murray on 05/17/2003 00:21:08

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 11:15:40

DECLARE @People TABLE ([ID] INT, [Name] NVARCHAR(25))

INSERT INTO @People([ID],[Name])
SELECT 1, 'john' UNION ALL
SELECT 2, 'alex'

DECLARE @Groups TABLE ([ID] INT, [Name] NVARCHAR(25))

INSERT INTO @Groups([ID],[Name])
SELECT 1, 'GROUPA' UNION ALL
SELECT 2, 'GROUPB' UNION ALL
SELECT 3, 'GROUPC' UNION ALL
SELECT 4, 'GROUPD'

DECLARE @PeopleGroups TABLE (PERSON_ID INT, GROUP_ID INT)

INSERT INTO @PeopleGroups(PERSON_ID,GROUP_ID)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 3

DECLARE @PersonName NVARCHAR(25)
SELECT @PersonName = 'john'

SELECT A.[ID], A.[Name]
FROM
@Groups A
LEFT JOIN @PeopleGroups B ON A.[ID] = B.GROUP_ID
LEFT JOIN @People C ON B.PERSON_ID = C.[ID] AND C.Name = @PersonName
WHERE C.[Name] IS NULL

murray
Starting Member

2 Posts

Posted - 2003-05-17 : 16:49:06

hey valter

this will work - but i was hoping to keep the query a simple join query and not resort to union stuff! is there no way this query can be made to work as with joins only?

all the best

murray

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-17 : 17:06:07

I think what Valter was demonstrating is that Todd's answer works correctly.
The only unions are the ones used to set up test data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources