Home » SQL & PL/SQL » SQL & PL/SQL » Help required in using IN clause
icon5.gif  Help required in using IN clause [message #217877] Mon, 05 February 2007 23:30 Go to next message
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 #217880 is a reply to message #217877] Mon, 05 February 2007 23:41 Go to previous messageGo to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi,
What error are you getting.

Re: Help required in using IN clause [message #217881 is a reply to message #217880] Mon, 05 February 2007 23:44 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
ORA-00920: Invalid relational Operator
Re: Help required in using IN clause [message #217883 is a reply to message #217877] Mon, 05 February 2007 23:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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
Re: Help required in using IN clause [message #217897 is a reply to message #217895] Tue, 06 February 2007 00:38 Go to previous message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
thanks
Previous Topic: %type variables
Next Topic: Query with multiple inner rows
Goto Forum:
  


Current Time: Wed Dec 07 06:44:21 CST 2016

Total time taken to generate the page: 0.10915 seconds