 Help required in using IN clause [message #217877] |
Mon, 05 February 2007 23:30  |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
I want to compare more than one columns in a table with similar columns in the other table, however using IN clause in the following manner gives an error eg.
select * from A
where aid,rid in (select aid,rid from B
where answerval in(7,8,9,10));
what can I do to get the result?
Thanks
|
|
|
|
|
|
|
|
| Re: Help required in using IN clause [message #217883 is a reply to message #217877] |
Mon, 05 February 2007 23:54  |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
Hi,
select * from A
where aid in ( select aid from B
where answerval in(7,8,9,10)
AND rid in (select rid from B
where answerval in(7,8,9,10) ;
I didnot try this but it should give you the same result.
In this case aid and rid are both compare with aid and rid
of B table .
If it works then do reply
Thanks
|
|
|
|
| Re: Help required in using IN clause [message #217891 is a reply to message #217883] |
Tue, 06 February 2007 00:12 |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
|
I have tried this and it does work , but in case i have to compare lot of columns then the query would be very lengthy and also there will be lot of subqueries. This will be time consuming , do we have any other option?
|
|
|
|
| Re: Help required in using IN clause [message #217895 is a reply to message #217891] |
Tue, 06 February 2007 00:33 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You have to group your columns by using parentheses:
SQL> create table faq1 (id number, text varchar2(10));
Table created.
SQL> create table faq2 (id number, text varchar2(10));
Table created.
SQL> select *
2 from faq1
3 where (id, text) in (select id
4 , text
5 from faq2
6 where id > 3
7 )
8 /
no rows selected |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
