Home » SQL & PL/SQL » SQL & PL/SQL » Query help (selecting diff field from same row)
Query help (selecting diff field from same row) [message #219458] Wed, 14 February 2007 09:00 Go to next message
cochranjd
Messages: 2
Registered: February 2007
Junior Member
Okay, if I had a table called "People" and in that table, there were the following fields

People_id, Name, Age, Parent

and instead of Parent being a person's name, it is a "People_id" for the record that is this person's parent -- how could I run a query to actually put that person's name in my return?

If I have:

1 John 8 3
2 Jim 23
3 Sarah 34


When I query, I want to see:

John, 8, Sarah
Jim, 23,
Sara 34,

I have no control over the design of the database.

Thanks.
Re: Query help (selecting diff field from same row) [message #219460 is a reply to message #219458] Wed, 14 February 2007 09:07 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
One option is to use a correlated subquery for the 3rd column
Re: Query help (selecting diff field from same row) [message #219462 is a reply to message #219458] Wed, 14 February 2007 09:09 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Another would be self join with Parent = People_Id
By
Vamsi
Re: Query help (selecting diff field from same row) [message #219469 is a reply to message #219458] Wed, 14 February 2007 09:51 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from test_person;

 PEOPLE_ID NAME                        AGE     PARENT
---------- -------------------- ---------- ----------
         1 John                          8          3
         2 Jim                          23
         3 Sara                         34

SQL> select name, age, (select a.name from test_person a
  2                             where a.people_id = b.parent) parent
  3  from test_person b;

NAME                        AGE PARENT
-------------------- ---------- --------------------
John                          8 Sara
Jim                          23
Sara                         34
Re: Query help (selecting diff field from same row) [message #219470 is a reply to message #219458] Wed, 14 February 2007 09:54 Go to previous message
cochranjd
Messages: 2
Registered: February 2007
Junior Member
Thanks for everyones help!

That will work.

Appreciate it!
Previous Topic: Granting privileges on trigger
Next Topic: Tuning PL/SQL with Collections
Goto Forum:
  


Current Time: Mon Dec 05 13:08:58 CST 2016

Total time taken to generate the page: 0.16058 seconds