IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Join and Count


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Sat Apr 05, 2008 12:52 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat Apr 05, 2008 1:38 am
Reply with quote

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
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Sat Apr 05, 2008 2:11 am
Reply with quote

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
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Sat Apr 05, 2008 7:55 am
Reply with quote

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
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Sat Apr 05, 2008 8:35 am
Reply with quote

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
View user's profile Send private message
Richa Jain

New User


Joined: 18 Mar 2008
Posts: 35
Location: Gurgaon

PostPosted: Sat Apr 05, 2008 9:40 am
Reply with quote

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
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Mon Apr 07, 2008 12:28 am
Reply with quote

Is it possible to use NOT EXISTS in my SQL query..Please guide
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Mon Apr 07, 2008 12:32 am
Reply with quote

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
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Mon Apr 07, 2008 12:44 am
Reply with quote

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
View user's profile Send private message
Ajesh

New User


Joined: 03 Apr 2008
Posts: 6
Location: Chennai

PostPosted: Tue Apr 08, 2008 2:48 pm
Reply with quote

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
View user's profile Send private message
Ajesh

New User


Joined: 03 Apr 2008
Posts: 6
Location: Chennai

PostPosted: Tue Apr 08, 2008 2:50 pm
Reply with quote

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
View user's profile Send private message
senthil_hari

New User


Joined: 27 Mar 2008
Posts: 7
Location: chennai

PostPosted: Wed Apr 09, 2008 4:08 pm
Reply with quote

hi ajesh,

pls tell me for getting the count of the result for your query.
Back to top
View user's profile Send private message
Ajesh

New User


Joined: 03 Apr 2008
Posts: 6
Location: Chennai

PostPosted: Wed Apr 09, 2008 4:14 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Count the number of characters in a f... CA Products 1
No new posts Split large FB file based on Key coun... DFSORT/ICETOOL 4
No new posts Find a record count/numeric is multip... COBOL Programming 1
No new posts Join files where value in one is betw... DFSORT/ICETOOL 6
Search our Forums:

Back to Top