querie problem any takers? - 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

Other Forums

Other Topics

querie problem any takers?

Author

Topic

n0i0x
Starting Member

5 Posts

Posted - 2002-03-01 : 12:07:51

Hi I'm using oracle 8.0i and have built amongst others the following tables. Named PROJECT, EMPLOYEE, AND CONTRIBUTOR (LINKS PROJECT & EMPLOYEE)

PROJNO PROJNAME PLDRNIN BUDGET
---------- ------------------------- -------------- ---------- -----SPENT
----
STARTDATE EXFINISH
--------- ---------
1 EXCITING SOFTWARE YK111111D 10000 5000
01-JAN-02 06-AUG-02

99 BRILLIANT SOFT YK222222D 100000 2000
02-FEB-01 08-JUN-02

EMPNIN LASTNAME FIRSTNAME DIVNO SAL
-------------- -------------- -------------- ---------- ----------
JOB MGRNIN
-------------- --------------
YR333333R SMITH BENJAMIN 3 12000
PROGRAMMER YR000002D

YR222222R JONES MICHAEL 1 12500
ANALYST YR000002D

YR777777R QUICK PAMELA
PROGRAMMER

YK111111D CROCKETT KEELEY
PROJECT LEADER

SQL> SELECT *
2 FROM CONTRIBUTOR;

PROJNO EMPNIN
---------- --------------
1 YR333333R
1 YR222222R
1 YR444444R
1 YR888888R
99 YR333333R
99 YR777777R
99 YR100000R
99 YR200000R

I want to list any project names which are implemented by both a 'PROGRAMMER' AND an 'ANALYST'?

So far my querie looks like this

SELECT p.projname
FROM project p, contributor c, employee e
WHERE p.projno = c.projno
AND e.empnin = c.empnin
AND e.job='ANALYST'
AND e.job='PROGRAMMER';

But this shows project names of those implemented by analyst or programmer and not those which contain both.

Any idea's?

Much appreciated

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-01 : 12:15:53

Moved to other topics since it's Oracle.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Jay99

468 Posts

Posted - 2002-03-01 : 12:16:05

SELECT p.projname
FROM project p inner join contributor c on p.projno = c.projno
WHERE exists
(select 'bling' from employee where empnin = c.empnin and job = 'ANALYST')
and exists
(select 'bling' from employee where empnin = c.empnin and job = 'PROGRAMMER')

Jay

n0i0x
Starting Member

5 Posts

Posted - 2002-03-01 : 12:40:49

Thanks for the reply.

I tried the code (modified to work with sqlplus)

SQL> SELECT p.projname
2 FROM project p, contributor c
3 WHERE p.projno = c.projno
4 AND exists
5 (select 'bling' from employee where empnin = c.empnin and job='ANALYST')
6 and exists
7 (select 'bling' from employee where empnin = c.empnin and job='PROGRAMMER');

no rows selected

But no rows were selected?
Can you tell me what I'm doing worng?

Jay99

468 Posts

Posted - 2002-03-01 : 13:22:55

...you right ... so sorry ... a little trigger happy today ...

try

select p.projname
from
(select projname
from project p inner join contributor c on p.projno = c.projno
inner join employee e on (c.empnin = e.empnin and job = 'programmer'))p
inner join
(select projname
from project p inner join contributor c on p.projno = c.projno
inner join employee e on (c.empnin = e.empnin and job = 'analyst'))a
on p.projname = a.projname

Jay

n0i0x
Starting Member

5 Posts

Posted - 2002-03-01 : 14:52:33

That's great my querie works!!

Thanks for all your help:)

n0i0x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources