SubQuery - complex syntax

[JGresko]

I'm trying to pull one record from one table that has a lot of referrences to other tables in it. I would like to be able to pull in the values from the other tables, but I can't do a normal join because multiple fields in the main table reference the same field (but different records) in the other tables.

Like this:

SELECT
     T1.MainID as  "Main ID",
     T1.INTREF as  "Internal reference",
     T1.fileNu as  "File",
     T1.AGEone as  "Agent 1",
     T1.AGEtwo as  "Agent 2",
     T1.AGEthr as  "Agent 3"
FROM TABLE1 T1 
WHERE T1.IDEBRE = '5'

Now the three agents are just reference numbers to another table where the agent info is kept. I do not want what is above, I what something like

SELECT
     T1.MainID as  "Main ID",
     T1.INTREF as  "Internal reference",
     T1.fileNu as  "File",

pass in T1.AGEone to
(SELECT T2.aName FROM TABLE2 T2, TABLE1 T1
WHERE T1.AGEone = T2.AGEID)
as "Agent 1",

pass in T1.AGEtwo to
(SELECT T2.aName FROM TABLE2 T2, TABLE1 T1
WHERE T1.AGEtwo = T2.AGEID)
as "Agent 2",

pass in T1.AGEthr to
(SELECT T2.aName FROM TABLE2 T2, TABLE1 T1
WHERE T1.AGEthr = T2.AGEID)
as "Agent 3",

FROM TABLE1 T1 
WHERE T1.IDEBRE = '5'

This is just an example! The real main table does this a number of times with different tables.

Is there a way to do this???


Thanks for any help!

Judy

 

[DaPi]

Something like this?

SELECT T1.MainID, A1.aName, A2.aName, A3.aName, etc
FROM TABLE1 T1, TABLE2 A1, TABLE2 A2, TABLE2 A3
WHERE T1.AGEone = A1.AGEID(+)
AND T1.AGEtwo = A2.AGEID(+)
AND T1.AGEthr = A3.AGEID(+)
AND etc

(I made them outer joins 'coz you might have codes missing? If that's never the case just remove the (+) from the conditions) DaPi - no silver bullet

 

[JGresko]

No, that won't work. This is not an AND because it would return nothing and an OR will cause cartesianing (sp). I only want one main record returned with the correct agent names inserted. It will be three different agents, therefore three different records from T2... an AND would not return anything because the UID can never be all three agents.

Thanks anyway

 

[JGresko]

Oh, wait... after taking a closer look, I just noticed what you did... you are right!

Sorry about the last message. Thanks!!

Judy