Kyungeun Kim Kyungeun Kim - 11 months ago 23
SQL Question

[ORACLE]How can I get the foreign key values using inline view?

What I'm trying to do is get the values using inline view.
on my table "mainBoard" has columns that tgno1,tgno2,tgno3, which are foreign keys refer the table,"Tag".
the table "Tag" has only two columns tgno(NUMBER), tgname(VARCHAR2).

I wanna return values from tgno(NUMBER) to tgname(VARCHAR2) using inline view.

here's what I tried for only one value:

FROM (SELECT tgname as tag1
FROM (SELECT tgname FROM tag WHERE tgno=1));

I wanna get all three values like:

SELECT tag1, tag2, tag3

the final goal is to get the values of tgname values instead of tgno1,2,3 bellow:

SELECT bno,bsubject,mno,bdate,bhit,bvote,tgno1,tgno2,tgno3,num
FROM (SELECT bno,bsubject,mno,bdate,bhit,bvote,tgno1,tgno2,tgno3,rownum as num
FROM (SELECT bno,bsubject,mno,bdate,bhit,bvote,tgno1,tgno2,tgno3
FROM mainBoard WHERE btno=1 ORDER BY bno DESC ))
WHERE num BETWEEN #{start} AND #{end}

Hope my question is understandable. Thank you in advance


You could join mainboard to tag thrice once for each foreign key relationship to tag in mainboard. Why do you have to have an inline view or was that just your approach?

SELECT MB.*, T1.tgName as Tag1Name, T2.tgName as Tag1Name, T3.tgName as Tag1Name
FROM mainboard MB
LEFT JOIN tag t1
 on T1.tgno = mb.tgno1
LEFT JOIN tag t2
 on T2.tgno2 = mb.tgno1
LEFT JOIN tag t3
 on T3.tgno3 = mb.tgno1
WHERE MB.num BETWEEN #{start} AND #{end}

I used left joins as I don't know if all 3 tag fields in MainBoard are always populated and assumed you wanted all mainboards regardless if tagno is populated in all 3.