'Recursive?' Query 1

[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

 

[sunila7]

Hi,

Try this query... i am using SQL server


SELECT TBL.FNAME,TBL.LNAME,
C.FName,C.LName
FROM
(select FNAME,LNAME,ML.Marriage_Cust_ID Cust_id
from customer c, Marriage_Link ML
where c.cust_id = ML.Cust_id)
TBL,
Customer C
Where TBL.Cust_id = C.Cust_ID


Sunil

 

[angiole]

Another approach you may is to call the same table twice with an alias as follows:

select m1.cust_id
,m1.fname
,m1.lname
,m2.cust_id
,m2.fname
,m2.lname
from customer m1
,marriage_link mlink
customer m2
where m1.cust_id = mlink.cust_id
and mlink.marriage_cust_id = m2.cust_id

AA :~)