| Information on SELCT Query [message #21693] |
Fri, 23 August 2002 03:12  |
Abhijith
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
Hello,
Please have a look at following query
SELECT ID FROM TAB1 WHERE NAME = 'A'
UNION
SELECT ID FROM TAB2 WHERE NAME = 'A'
so above query returns the combination of first set and second set query. But i have an little change in requirement where i want select a row from either of the set. Following are the possible cases:
1. If it finds row for the first set, then i dont want to select a row from second set.
2. If it doesnot find row in first set, then i want to select a row from second set.
3. If it doesnot find row in both the sets, then ofcourse no rows r selected.
And moreover, how can i do sorting for the selected rows with the above query?
Kindly suggest me as how to go about with the above requirement.
Thanks in advance
Regards,
Abhijith
|
|
|
|
| Re: Information on SELCT Query [message #21704 is a reply to message #21693] |
Fri, 23 August 2002 09:50  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Union only selects distinct values, so if a value is in A and in B it will only be returned once to you. UNION ALL will return both to you (it won't only return distinct rows). To order, simply add ORDER BY ID before the ';'
To do what you are asking doesn't make much sense, but you can obviously do it in PL/SQL with cursors. The reason it doesn't make much sense is that the algorithm must constider multiple rows meeting the criteria - not just a single row. For the multiple row case you will have a mixture or your rules.
|
|
|
|
| Re: Information on SELCT Query [message #21716 is a reply to message #21693] |
Sun, 25 August 2002 07:36 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Try with MINUS, rather than UNION. Since your query is not clear and your given example SELECT does not support your criteria and it is confusing, I am not able assume any thing. But I advise you to try with MINUS also.
SELECT * FROM (
(SELECT ID FROM TAB1 WHERE NAME = 'A'
MINUS
SELECT ID FROM TAB2 WHERE NAME = 'A')
UNION
(SELECT ID FROM TAB2 WHERE NAME = 'A'
MINUS
SELECT ID FROM TAB1 WHERE NAME = 'A')
);
I am not sure what it returns as your criteria. But try it out. If you still need help, come back with some example data.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
