how do i.. - 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)

how do i..

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-08-05 : 14:13:33

get a list of employees who worked on projects "A" and "B" from these tables.

Employee EmpProj Project
Id Name EmpId ProjID Id Name
1 Joe 1 1 1 A
2 james 1 2 2 B
3 Jack 2 1 3 C
2 2 4 D
3 1

The query should return Joe and James.

Just trying to polish my SQL Query skills. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 14:21:21

Do you know what DDL is?

I don't understand your sample Data....

Brett

8-)

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-05 : 14:21:49

Looks like you want A OR B, not AND.

SELECT *
FROM Employee e JOIN EmpProj ep ON e.ID = ep.EmpID
JOIN Project p ON ep.ProjID = p.ID
WHERE p.Name IN ('A', 'B')

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-08-05 : 14:28:30

Sorry should have previewed first. Here's my DDL:
Employee
ID Name
1 Joe
2 James
3 Jack

EmpProj
EmpId ProjID
1 1
1 2
2 1
2 2
3 1

Project
ID Name
1 A
2 B
3 C

Chad, I think your query would give me Joe, James, Jack, I only want Joe and James since they both worked on projects A and B, while Jack worked only on project A.

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-05 : 14:31:47

Oh, so you do want A and B, then just change the WHERE clause to say:
WHERE p.Name = 'A' AND p.Name = 'B'

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Edit, Actually, that's no good. Sorry, let me think

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-08-05 : 14:34:56

Chad, I tried that before and the query gave me no results. Why?

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-05 : 14:37:03

Because p.Name can't equal both A and B.

http://www.clrsoft.com

Software built for the Common Language Runtime.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-08-05 : 14:41:34

so this is not the correct where clause then?
WHERE p.Name = 'A' AND p.Name = 'B'

How sould I write the query then to give me what I want?

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-05 : 14:46:17

I was trying to write a query off the top of my head, but I need to test it. I'll post it when I get have it worked out.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-05 : 14:53:47

create table #Employee (ID int, Name varchar(10))
create table #empproj (empid int, projid int)
create table #project (ID int, Name varchar(10))

insert into #Employee VALUES (1, 'Joe')
insert into #Employee VALUES (2, 'James')
insert into #Employee VALUES (3, 'Jack')

insert into #Project VALUES (1, 'A')
insert into #Project VALUES (2, 'B')
insert into #Project VALUES (3, 'C')

insert into #EmpProj VALUES (1, 1)
insert into #EmpProj VALUES (1, 2)
insert into #EmpProj VALUES (2, 1)
insert into #EmpProj VALUES (2, 2)
insert into #EmpProj VALUES (3, 1)

SELECT *
FROM #Employee e
WHERE ID IN (SELECT ep1.EmpID FROM #EmpProj ep1 JOIN #EmpProj ep2 ON ep1.empID = ep2.EmpID
JOIN #Project p1 ON ep1.ProjID = p1.ID
JOIN #Project p2 ON ep2.ProjID = p2.ID
WHERE p1.Name = 'A' AND p2.Name = 'B')

drop table #employee
drop table #empproj
drop table #project

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 15:01:41

Chad...we work to hard....

Brett

8-)

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-05 : 15:10:16

Yeah. I originally thought it was a very simple query. But when I realized my original answer was incorrect, I couldn't leave him hanging.

For future reference PGG123, DDL is Data Definition Language. It is the Create statements for your tables. If you post that (Like the creates and inserts in my query) it will save time for us having to create it, and you will probably get more (and quicker) responses. It also makes your question clearer.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-08-05 : 15:50:30

thanks, Chad.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-08-06 : 01:02:47

Ok thanks to Chad again. His query gives me what I want. But will someone pls explain how the query works? I'd like to know so I know what to do when I encounter similar/the same type of a problem.

Can I just replace the where clause with this:
WHERE p1.Name = 'A' AND p2.Name <> 'B'
if I want to get the employees that worked on A but not on B? i tried it and I didn't get the desired results. I should get Jack only.

create table #Employee (ID int, Name varchar(10))
create table #empproj (empid int, projid int)
create table #project (ID int, Name varchar(10))

insert into #Employee VALUES (1, 'Joe')
insert into #Employee VALUES (2, 'James')
insert into #Employee VALUES (3, 'Jack')

insert into #Project VALUES (1, 'A')
insert into #Project VALUES (2, 'B')
insert into #Project VALUES (3, 'C')

insert into #EmpProj VALUES (1, 1)
insert into #EmpProj VALUES (1, 2)
insert into #EmpProj VALUES (2, 1)
insert into #EmpProj VALUES (2, 2)
insert into #EmpProj VALUES (3, 1)
insert into #EmpProj VALUES (1, 3)
insert into #EmpProj VALUES (3, 3)

SELECT *
FROM #Employee e
WHERE ID IN (SELECT ep1.EmpID FROM #EmpProj ep1 JOIN #EmpProj ep2 ON ep1.empID = ep2.EmpID
JOIN #Project p1 ON ep1.ProjID = p1.ID
JOIN #Project p2 ON ep2.ProjID = p2.ID
WHERE p1.Name = 'A' AND p2.Name <> 'B')

drop table #employee
drop table #empproj
drop table #project

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources