| View previous topic :: View next topic |
| Author |
Message |
batu544
Beginner
Joined: 02 Aug 2005
Posts: 75
Topics: 27
|
 Posted: Sat Jan 31, 2009 2:20 am Post subject: Query to get specific result. |
 |
|
Hi,
Here is one requirement.
Table 1
| Code: |
E_ID E_NAME
------- -------------------
10 PAUL
20 SAM
30 DENIS
40 MERTZ
|
Table 2
| Code: |
E_ID1 E_SALARY
-------- ---------------
10 100000
20 200000
20 250000
30 300000
50 500000
60 600000
|
Now the required output is
| Code: |
E_id E_NAME e_SALARY
-------- ------------ ------------
10 PAUL 100000
20 SAM 250000
30 DENIS 300000
40 MERTZ -
|
just note : all the rows from table1 is required in the output. if there is any duplicate e_id1 in table2 then it should take the row having highest salary.
I have done this by creating one intermediate table and using LEFT JOIN. ( because I got some error while running left join with an inner select query. (may be its not allowed.)
Is it possible to get the desired result by using UNION ?
e.g.
| Code: |
SELECT E_ID, E_NAME
,E_ID1, E_SALARY
FROM TABLE1,TABLE2
WHERE E_ID = E_ID1
UNION
SELECT E_ID, E_NAME
,E_ID1,E_SALARY
FROM TABLE1,TABLE2
WHERE
0 = ( SELECT COUNT(*) FROM .... )
|
I am not sure .. what to write in the 2nd part of UNION ..
Could somebody help me on this .....
Thanks
_________________
Regards,
batu |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Feb 01, 2009 2:26 pm Post subject: |
|
|
batu544,
Try this untested sql.
| Code: |
SELECT A.E_ID
,A.E_NAME
,B.SAL
FROM TABLE1
,(SELECT E_ID1 AS ID1
,MAX(E_SALARY) AS SAL
FROM TABLE2
GROUP BY E_ID1) B
WHERE A.E_ID = B.ID1
;
|
This is based on the sql I posted in here
http://www.mvsforums.com/helpboards/viewtopic.php?p=49559#49559
Hope this helps...
Cheers
Kolusu |
|
| Back to top |
|
|
batu544
Beginner
Joined: 02 Aug 2005
Posts: 75
Topics: 27
|
| Posted: Sun Feb 01, 2009 4:42 pm Post subject: |
|
|
Hi Kolusu,
Thanks for your help.. but its not giving the desired result. Its not selecting the E_ID's which are not present in table2 ( e.g. e_id = 40) ..
The output is coming like this only ..
| Code: |
E E
ID NAME
----------- ------------------------------
10 PAUL
20 SAM
30 DENIS
|
Just want to mention one more point .. If the table2 has more than 3 or 4 columns and I don't require the salary column in my output, then what will be the query for this.
Here I am writing the table2 again..
| Code: |
E_ID1 E_SALARY E_PLACE
--------- ------------ --------------
10 100000 ST LOUIS
20 200000 MISSOURI
20 250000 CA
30 300000 MO
50 500000 CA
60 600000 XX
|
I have written the following query to the desired result..
| Code: |
SELECT E_ID, E_NAME
, E_ID1, E_PLACE
FROM TAB1
, TAB2 A
WHERE E_ID = E_ID1
AND E_SALARY = ( SELECT MAX(E_SALARY) FROM TAB2
WHERE E_ID1 = A.E_ID1 )
|
This is also giving me only 10,20 and 30 e_id's .. not the 40 one..  so I was planning to do an UNION/UNION ALL with another query.. but no idea what to write in another so that I will get only the rows which are present in table1.
I have tried to do one LEFT JOIN with query, but it was giving error due to the inner query..
( I was trying to edit the 1st post to add another 1 or 2 column but not able to do that. sorry for updating the requirement again..  )
_________________
Regards,
batu |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Mon Feb 02, 2009 12:28 am Post subject: |
|
|
Why can't you just use the LEFT join and the MAX in one simple query?
untested sql
| Code: | SELECT E_ID
, E_NAME
, E_ID1
, MAX(E_SALARY)
FROM TABLE1
LEFT INNER JOIN TABLE2
ON E_ID = E_ID1
GROUP BY E_ID
, E_NAME
, E_ID1 |
|
|
| Back to top |
|
|
batu544
Beginner
Joined: 02 Aug 2005
Posts: 75
Topics: 27
|
| Posted: Mon Feb 02, 2009 5:32 am Post subject: |
|
|
This will work if I need E-salary on my result. .. but I don't want e_salary on my output result.. but the selected row should have max e_salary ..
_________________
Regards,
batu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Feb 02, 2009 11:21 am Post subject: |
|
|
batu544,
Unless I am missing something , I have no idea of what you are trying to do. Why don't you show us a sample input for both tables and also the desired output for all cases.
Kolusu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Feb 02, 2009 3:55 pm Post subject: |
|
|
Batu544,
After reading it once more , I think your intention is to get the non matching rows also from table1 along with matching rows
Try this untested sql which would put a zero for salary for any non matching row in table2
| Code: |
SELECT A.E_ID
,A.E_NAME
,SUM(CASE WHEN A.E_ID = B.ID1 THEN B.SAL
ELSE DEC(0) END)
FROM TABLE1 A
,(SELECT E_ID AS ID1
,MAX(E_SALARY) AS SAL
FROM TABLE2
GROUP BY E_ID) B
GROUP BY A.E_ID
,A.E_NAME
; |
|
|
| Back to top |
|
|
Sqlcode
Intermediate
Joined: 15 Dec 2006
Posts: 157
Topics: 38
|
| Posted: Mon Feb 02, 2009 4:16 pm Post subject: |
|
|
Try this Untested..
| Code: |
WITH TEMP_PASS AS
(
SELECT A.E_ID, A.E_NAME, MAX(COALESCE(B.E_SALARY,0))
FROM TABLE1 A,
TABLE2 B
WHERE A.E_UD = B.E_ID1
AND (
GROUP BY E_ID
, E_NAME
, E_ID1
, B.E_SALARY)
SELECT * FROM TEMP_PASS;
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Feb 02, 2009 4:32 pm Post subject: |
|
|
Sqlcode,
Did you realize that OP wants both matched as well as unmatched records from table1? And your Where clause will eliminate the Eid=40 record.
Just out of curiosity Is that a z/OS DB2 Syntax?
Last edited by kolusu on Mon Feb 02, 2009 4:43 pm; edited 1 time in total |
|
| Back to top |
|
|
Sqlcode
Intermediate
Joined: 15 Dec 2006
Posts: 157
Topics: 38
|
| Posted: Mon Feb 02, 2009 4:42 pm Post subject: |
|
|
I think he doesn't want UNMATCHED records but rather If records are not present in TABLE2 then he would want either NULLS or ZEROS in Salary Field.
So its like PARENT (TABLE1) and CHILD (TABLE2) relationship, if a records in not present in CHILD, then he wants to see parent record but NULLS or ZEROS in E_SALARY field.
This is my understanding and I may be Wrong. |
|
| Back to top |
|
|
Sqlcode
Intermediate
Joined: 15 Dec 2006
Posts: 157
Topics: 38
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
