Question on Comparing Two Select Statements [message #20466] |
Mon, 27 May 2002 04:36 |
Laxman Chepuri
Messages: 8 Registered: May 2002
|
Junior Member |
|
|
aay
I have two tables
1. tbl_first
columns: value1,value2,value3
Apply some condition on value3 retrieve value1,value2
2. tbl_second
columns: value1,value2,value4
Apply some condition on value4 retrieve value1,value2
value1,value2 in both two tables same with same datatype and same size but values are different
Now, I want to get unique rows of those two select statement
query1: select value1,value2 from tbl_first where
value3=(condition)
output
value1 value2
1001 xyz
1002 abc
query2 : select value1,value2 from tbl_second where
value4=(condition)
output
value1 value2
1001 xyz
1003 abc
Now I want to compare these two result sets and get matched values
I am needed output is
value1 value2
1001 xyz
How Can I get with a single Sql statement
|
|
|
|
|
|
Re: Question on Comparing Two Select Statements [message #20479 is a reply to message #20466] |
Tue, 28 May 2002 05:39 |
sridhar
Messages: 119 Registered: December 2001
|
Senior Member |
|
|
1. Intersect should work.
2. Senthil's SQL needs additional conditions. As Laxman mentioned that table1 and table2 have specific conditional statements on value3 and 4 respectively.
If we add them, it should work fine.
3. Here is just another alternative,
SELECT col1, col2 FROM
(
SELECT value1 col1, value2 col2 from table1
WHERE value3 = (condition)
UNION ALL
SELECT value1 col1, value2 col2 from table2
WHERE value4 = (condition)
) GROUP BY col1, col2 HAVING count(*) > 1;
Thx,
Sri
|
|
|