CODEWITHSUNDEEP
sqloracle-database
user1501309
user1501309

Reputation: 33

inner query to return more than one row

I am using oracle 11i in sql developer..

this is my query:

SELECT itm.stu_no,wm_concat(uif.names) 
FROM personal uif,Coll itm, Dep ihts
WHERE uif.stu_id IN('26',    '29',    '32')   
  and (uif.stu_no,uif.stu_no) in 
  (
       select itm.stu_no,itm.stu_test_no 
       from Col itm,Dep ihts  
       where itm.stu_no = ihts.stu_no
       AND itm.status = 'A'
       AND ihts.status = 'A'
  )
  AND itm.status = 'A'
  and  itm.stu_no=ihts.stu_no and ihts.status = 'A'
  group by itm.stu_no;

the inner query (marked as strong text) returns...

stu_no             stu_test_no
-----------------------------------
123                 234

but the complete query is not returning any rows.

this is the expected result..

stu_no              wm_concat(names)
------------------------------------------
123                  James Alex
234                  (null)

Is there any way to get the expected result?

Upvotes: 0

Views: 162

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16915

It doesn't work because you're asking uif.stu_no to be both 123 and 234:
(123,123) is not in ((123,234))
and
(234,234) is not in ((123,234))

you can try this: 

SELECT itm.stu_no,wm_concat(uif.names) 
FROM personal uif,Coll itm, Dep ihts
WHERE uif.stu_id IN('26',    '29',    '32')   
  and uif.stu_no in 
  (
       select itm.stu_no 
       from Col itm,Dep ihts  
       where itm.stu_no = ihts.stu_no
       AND itm.status = 'A'
       AND ihts.status = 'A'
       union all
       select itm.stu_test_no 
       from Col itm,Dep ihts  
       where itm.stu_no = ihts.stu_no
       AND itm.status = 'A'
       AND ihts.status = 'A'
  )
  AND itm.status = 'A'
  and  itm.stu_no=ihts.stu_no and ihts.status = 'A'
  group by itm.stu_no;

But why not: 

SELECT itm.stu_no,wm_concat(uif.names) 
FROM personal uif, Coll itm, Dep ihts
WHERE uif.stu_id IN('26',    '29',    '32')   
  and (uif.stu_no = itm.stu_no OR uif.stu_no = itm.stu_test_no)
  AND itm.status = 'A'
  and itm.stu_no = ihts.stu_no 
  and ihts.status = 'A'
  group by itm.stu_no;

Upvotes: 1

Related Questions