View previous topic :: View next topic
|
Author |
Message |
Unique
New User
Joined: 10 Jul 2007 Posts: 24 Location: Blore
|
|
|
|
Hi,
i have 2 tables and i am facing problem clubbing them
table1
Code: |
EMPNO EMP_DETAILS
1111 AAAAA
2222 BBBBB
3333 CCCC
4444 DDDD
5555 EEEEE
6666 FFFFF |
table2
Code: |
PROJECT PROJECT_DET EMPNO GRADE_GIVEN
P1 PAAAAAAAA 1111 A
P1 PAAAAAAAA 3333 B
P1 PAAAAAAAA 4444 X
P1 PAAAAAAAA 2222 X
P2 PBBBBBBBB 2222 A
P2 PBBBBBBBB 1111 A
P2 PBBBBBBBB 3333 B |
I need the employees details along with the project details where projects doesnt have a grade X
for eg:
Code: |
BBBB P2 PBBBBBBBB 2222 A
AAAA P2 PBBBBBBBB 1111 A
CCCC P2 PBBBBBBBB 3333 B |
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Why has the posted output been selected? It does not appear to be complete? Why is P1 AAAA not in the output?
Please review your inputs and output and re-post the "rules" you need to implement.
Also, please use the "Code" tag at the top of the reply panel to align your post - it makes it much more readable.
I've "Coded" your data and deleted the 'extra' posts in your topic. |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
My read is that the P1 project does have a grade X........I think that is why the count in the title..... |
|
Back to top |
|
|
Unique
New User
Joined: 10 Jul 2007 Posts: 24 Location: Blore
|
|
|
|
Hi,
i have 2 tables and i am facing problem clubbing them
table1
Code: |
EMPNO EMP_DETAILS
1111 AAAA
2222 BBBB
3333 CCCC
4444 DDDD
5555 EEEE
6666 FFFF |
table2
Code: |
PROJECT PROJECT_DET EMPNO GRADE_GIVEN
P1 PAAAAAAAA 1111 A
P1 PAAAAAAAA 3333 B
P1 PAAAAAAAA 4444 X
P1 PAAAAAAAA 2222 X
P2 PBBBBBBBB 2222 A
P2 PBBBBBBBB 1111 A
P2 PBBBBBBBB 3333 B |
I need the employees details along with the project details where projects doesnt have a grade X .
for eg:
Code:
Code: |
EMP_DETAILS PROJECT PROJECT_DET EMPNO GRADE_GIVEN
BBBB P2 PBBBBBBBB 2222 A
AAAA P2 PBBBBBBBB 1111 A
CCCC P2 PBBBBBBBB 3333 B
|
i am not sure how we need to use COUNT to get the projects whichb doesnt have a grade X. |
|
Back to top |
|
|
Unique
New User
Joined: 10 Jul 2007 Posts: 24 Location: Blore
|
|
|
|
hi
Please note that Project P1 has given a grade X to one of EMP while P2 has grades A and B only.So,in my output,i have only P2 along with EMP_DETAILS
Regards |
|
Back to top |
|
|
Richa Jain
New User
Joined: 18 Mar 2008 Posts: 35 Location: Gurgaon
|
|
|
|
Code: |
SELECT E.EMP_DETAILS, P.PROJECT , P.PROJECT_DET, E.EMPNO, P.GRADE_GIVEN FROM EMP_DETAILS E PROJECT P WHERE E.EMPNO = P.EMPNO AND PROJECT <> (SELECT PROJECT FROM PROJECT WHERE GRADE_GIVEN = X GROUP BY PROJECT) |
|
|
Back to top |
|
|
Unique
New User
Joined: 10 Jul 2007 Posts: 24 Location: Blore
|
|
|
|
Is it possible to use NOT EXISTS in my SQL query..Please guide |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
Unique wrote: |
Is it possible to use NOT EXISTS in my SQL query..Please guide |
What does your query look like with NOT EXISTS? |
|
Back to top |
|
|
Unique
New User
Joined: 10 Jul 2007 Posts: 24 Location: Blore
|
|
|
|
Quote: |
SELECT E.EMP_DETAILS, P.PROJECT , P.PROJECT_DET, E.EMPNO, P.GRADE_GIVEN FROM EMP_DETAILS E PROJECT P WHERE E.EMPNO = P.EMPNO AND PROJECT <> (SELECT PROJECT FROM PROJECT WHERE GRADE_GIVEN = X GROUP BY PROJECT) |
Is it possible to use "NOT EXISTS " in this
[/quote] |
|
Back to top |
|
|
Ajesh
New User
Joined: 03 Apr 2008 Posts: 6 Location: Chennai
|
|
|
|
Please try this out.Hope this will solve.
SELECT T1.EMP_DETAILS,
T2.PROJECT,
T2.PROJECT_DET,
T1.EMPNO,
T2.GRADE_GIVEN
FROM EMP_DETAILS T1,
PROJECT T2
WHERE T1.EMPNO = T2.EMP_NO
AND T2.GRADE_GIVEN <> 'X'
We can use does not exist in your query.Also you can try
GRADE_GIVEN NOT IN ('X')
Thanks
Ajesh |
|
Back to top |
|
|
Ajesh
New User
Joined: 03 Apr 2008 Posts: 6 Location: Chennai
|
|
|
|
Do you want the count of the result set.If so just reply back.I can help you in getting the count.
Thanks
Ajesh |
|
Back to top |
|
|
senthil_hari
New User
Joined: 27 Mar 2008 Posts: 7 Location: chennai
|
|
|
|
hi ajesh,
pls tell me for getting the count of the result for your query. |
|
Back to top |
|
|
Ajesh
New User
Joined: 03 Apr 2008 Posts: 6 Location: Chennai
|
|
|
|
Hi Senthil,
This query fetches the result set count.
SELECT COUNT(*)
FROM
(SELECT T1.EMP_DETAILS,
T2.PROJECT,
T2.PROJECT_DET,
T1.EMPNO,
T2.GRADE_GIVEN
FROM EMP_DETAILS T1,
PROJECT T2
WHERE T1.EMPNO = T2.EMP_NO
AND T2.GRADE_GIVEN <> 'X' )A
Regards
Ajesh |
|
Back to top |
|
|
|