3-way exclusion query - 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 2000 Forums

SQL Server Development (2000)

3-way exclusion query

Author

Topic

atomz4peace
Starting Member

7 Posts

Posted - 2008-07-06 : 00:13:16

Can anyone help with a query I'm trying to make? I can do with with an ugly sub-query, but there must be a better way.

I have 3 tables the define an object. I have 3 "linking" tables to show the relationship. Let's say:

Table U for object U
id PK int
name varchar

Table G for object G
id PK int
name varchar

Table R for object R
id PK int
name varchar

The relationship tables are simply 2 column tables. Col 1 is the PK from one table (U, G, or R) and column 2 is the PK from the other table:

UG
uid int FK U.id
gid int FK G.id

UR
uid int FK U.id
rid int FK R.id

RG
rid int FK R.id
gid int FK G.id

So I'm trying to get something in english:

show me all R where uid = x, and all G where uid= x except for those gid that are in RG for the rid returned in the 1st query.

1) select R.* from R inner join UR on R.id = UR.rid where UR.uid = x

2) select G.* from G inner join UG on G.id = UG.gid where UG.uid = x and UG.gid NOT IN (select gid from RG inner join UR on UR.rid = RG.rid where UR.uid = x)

So I'll have 2 queries like this. It's query 2 that seems very inefficient and maybe there's a way to do with with an outer left join or something? Any ideas?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 01:54:42

[code]SELECT g.*
FROM G g
INNER JOIN UG ug
ON ug.gid=g.id
AND ug.uid='x'
INNER JOIN RG rg
ON rg.gid=ug.gid
LEFT JOIN
(SELECT r.*
FROM R r
INNER JOIN UR ur
ON r.id=ur.rid
AND ur.uid='x')t
ON t.id=rg.rid
WHERE t.id IS NULL[/code]

atomz4peace
Starting Member

7 Posts

Posted - 2008-07-09 : 00:38:49

I'll give it a try. Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources