PL/SQL Question - 'recursive'? query

[nibeck]

Not quite sure if 'recursive' is the right word or not, but here's what I have:

Customer Table
--------
Cust_ID (PK)
FName
LName
etc.
etc.

Marriage_Link table
-------------
Cust_ID (PK)
Marriage_Cust_ID

I need to query the Customer table based on a specified Cust_ID and the get FName, LName. I then need to link to the Marriage_Link table, get the Marriage_Cust_ID field, use that to re-query the Customer table to get the Fname, Lname of the spouse.

Ideas?

Thanks,

Mike

 

[sem]

select s.Fname, s.Lname, c.Fname, c.Lname
from customer c, mariage_link m, customer s
where m.cust_id=c.cust_id
and s.cust_id=m.Marriage_Cust_ID
and c.cust_id = <some_value>

 

[Turkbear]

Hi,
To make the results of sem's query a little more readable,
try using aliases and reordering the fields a bit:

select  c.Fname Cust_F_Name, c.Lname Cust_L_Name,s.Fname Spouse_F_Name, s.Lname Spouse_L_Name from customer c, mariage_link m, customer s
where m.cust_id=c.cust_id 
and s.cust_id=m.Marriage_Cust_ID
and c.cust_id = <some_value>

hth

 

[nibeck]

Excellent, Thanks!

- Mike