|
View previous topic :: View next topic
|
| Author |
Message |
batu544
New User
Joined: 21 Jul 2005
Posts: 17
Location: bangalore
|
|
|
|
| batu544 wrote: |
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 ALL
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 |
|
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
batu544,
How about trying a simple LEFT JOIN like this.
| Code: |
SELECT E_ID
,E_NAME
,MAX(E_SALARY)
FROM TABLE1
LEFT JOIN
TABLE2
ON E_ID = E_ID1
GROUP BY E_ID,E_NAME;
|
|
|
| Back to top |
|
|
batu544
New User
Joined: 21 Jul 2005
Posts: 17
Location: bangalore
|
|
|
|
Hi Arun,
Thanks for your reply.. but I hope this will not give me the expected result, when the table2 has more that 2 column and I need all the columns in my result.
Lets think that table2 contains following columns ..
| Code: |
E_ID1 E_SAARY E_PLACE
--------- ------------ --------------
10 100000 ST LOUIS
20 200000 MISSOURI
20 250000 CA
30 300000 MO
50 500000 CA
60 600000 XX
|
One more point is if I don't want e_salary column in my result then this query will not work.
For this I used to write the query like this..
| Code: |
SELECT E_ID, E_NAME
, E_ID1, E_SAL
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 query is giving the rows which are present in both the tables. If I am doing LEFT JOIN with this query, then its giving error.. due to that inner query ..
So, need help in writing the query in other way..  |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| but I hope this will not give me the expected result, |
This WILL give you the 'expected results' shown by you in your initial post.
| Quote: |
| One more point is if I don't want e_salary column in my result then this query will not work |
You cant expect a single sql to work for all the cases. Also you have not posted your 'new expected result'. I would suggest posting it so that somebody here might be able to help. |
|
| Back to top |
|
|
batu544
New User
Joined: 21 Jul 2005
Posts: 17
Location: bangalore
|
|
|
|
| arcvns wrote: |
| Quote: |
| but I hope this will not give me the expected result, |
This WILL give you the 'expected results' shown by you in your initial post. |
[/quote]
This is NOT giving the desired result when the table2 has more than 2 rows ..
I am posting the requirements here again..
table1
| Code: |
e_id e_name
----- -----------
10 xxxxx
20 yyyyy
30 zzzzz
40 aaaaa
|
table2
| Code: |
e_id1 e_salary e_place
----- ---------- ---------
10 100 bangalore
20 2000 pune
20 2500 mumbai
30 3000 Puri
50 5000 Test place
|
now the desired result is
| Code: |
e_id e_name e_palce
----- --------- ---------
10 xxxxxx bangalore
20 yyyyyy mumbai
30 zzzzzzz puri
40 aaaaaa --
|
Please note : All the rows from the table1 will be selected .. and details will be selected from table 2 for maximum salary only. ( e.g e_id = 20 ) ..
Could anyone please help me on this issue. ? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| You cant expect a single sql to work for all the cases. |
Totally agree. Even if some single query is found, it may not be the best (or even a good) solution. Some very innocent-looking "simple" queries have a tendancy to "dim the lights".
2 very simple queries inside a bit of simple program code will do what you need and will also perform well. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Quote: |
If I am doing LEFT JOIN with this query, then its giving error.. due to that inner query ..
|
What error is it giving? |
|
| Back to top |
|
|
|
|
