Prev: Date Function
Next: stroing results in a variable
From: dbuchanan on 16 Jul 2008 17:47
I need to find out what relationships do NOT exist in a join table

-- employee table
create table #E (emp int)

insert into #E (emp) values (1)
insert into #E (emp) values (2)
insert into #E (emp) values (3)

-- class table
create table #C (cls char(1))

insert into #C (cls) values ('a')
insert into #C (cls) values ('b')

-- classes attended
create table #J (emp int, cls char(1))

insert into #J (emp,cls) values (1,'a')
insert into #J (emp,cls) values (1,'b')
insert into #J (emp,cls) values (2,'a')

select * from #E
select * from #C
select * from #J

#J returns the following results:

emp,cls
1,a
1,b
2,a

Question: How can I construct a query to return pairs where no
relations exist?

Like this

emp,cls
2,b
3,a
3,b

To make a story out of this... #J shows what employees took what
class. What I need is the opposite ~ What employees did not take what
class. How do I construct a query to return this informaion?

From: Erland Sommarskog on 16 Jul 2008 18:51
dbuchanan (dbuchanan52(a)hotmail.com) writes:
> Question: How can I construct a query to return pairs where no
> relations exist?
>
> Like this
>
> emp,cls
> 2,b
> 3,a
> 3,b

First cross join #E with #C and eliminate those that took a course with
NOT EXISTS:

select e.emp, c.cls
FROM #E e
CROSS JOIN #C c
WHERE NOT EXISTS (SELECT *
FROM #J j
WHERE j.emp = e.emp
AND j.cls = c.cls)


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: dbuchanan on 16 Jul 2008 19:43
Erland
Thanks!

Doug
From: Alex Kuznetsov on 16 Jul 2008 21:44
On Jul 16, 5:51 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> dbuchanan (dbuchana...(a)hotmail.com) writes:
> > Question: How can I construct a query to return pairs where no
> > relations exist?
>
> > Like this
>
> > emp,cls
> > 2,b
> > 3,a
> > 3,b
>
> First cross join #E with #C and eliminate those that took a course with
> NOT EXISTS:
>
> select e.emp, c.cls
> FROM #E e
> CROSS JOIN #C c
> WHERE NOT EXISTS (SELECT *
> FROM #J j
> WHERE j.emp = e.emp
> AND j.cls = c.cls)
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

alternatively you can use an inner join:

select e.emp, c.cls
FROM #E e
INNER JOIN #C c
ON NOT EXISTS (SELECT *
FROM #J j
WHERE j.emp = e.emp
AND j.cls = c.cls)
From: Plamen Ratchev on 16 Jul 2008 21:57
Very similar to Erland's query, on SQL Server 2005 you can use the EXCEPT
operator:

SELECT E.emp, C.cls
FROM #E AS E
CROSS JOIN #C AS C
EXCEPT
SELECT emp, cls
FROM #J;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 |  Next  |  Last
Pages: 1 2
Prev: Date Function
Next: stroing results in a variable