Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » How to bring child subquery column values to parent for display (ORACLE, 9206, UNIX)
How to bring child subquery column values to parent for display [message #293067] Thu, 10 January 2008 15:15 Go to next message
ashokkum
Messages: 2
Registered: February 2007
Location: India
Junior Member

Hi

Table1:

SNo Name
---- ----
x1..... A
x2..... B
x3..... C

Table2:

SNo ParentSNo Type
---- ---------- ----
y1.......x1.......1
y2.......x1.......2
y3.......x2.......1
y4.......x3.......2

I would like to retrieve only the following output:

Name Type
---- -----
x1.......1 or 2 (any one)
x2.......1
x3.......2

My query is as below:

select t1.name, t2.type from Table1 t1, Table 2 t2 where
t1.sno = t2.parentsno
and t2.type in (1, 2)

and it retreives the results as like below:

Name Type
---- -----
x1.......1
x1.......2
x2.......1
x3.......2

The second record is not required and one x1 is sufficient as long as the x1 is a type of either 1 or 2

So, I would like to write it using subquery for a better understanding, but not sure how get the columns from the child subquery. i.e.

select name from Table1 where
sno in (select parentsno from Table2 where type in (1, 2))

which gives only:

Name
----
x1
x2
x3

I would like to also retrieve the type along with the name using subquery, can you help?

Thanks,
Ashok
Re: How to bring child subquery column values to parent for display [message #293318 is a reply to message #293067] Fri, 11 January 2008 12:17 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
If I'm not wrong you are getting
A.......1
A.......2
B.......1
C.......2
and you want to get
A 1 or 2
B 1
C 2

If so, you can search the forum on pivot or rows to columns.

By
Vamsi
Re: How to bring child subquery column values to parent for display [message #293707 is a reply to message #293318] Mon, 14 January 2008 13:08 Go to previous message
ashokkum
Messages: 2
Registered: February 2007
Location: India
Junior Member

That was exactly what I was searching for! Didn't know the term earlier and thanks for your guidance.

Ashok
Previous Topic: FireFox 2.0 is not compatible with OWS4.0.8
Next Topic: Multiple select in 'File Browse...'
Goto Forum:
  


Current Time: Thu Mar 28 13:46:22 CDT 2024