CODEWITHSUNDEEP
sql
Heidi Lilybeth
Heidi Lilybeth

Reputation: 127

Oracle/plsq Inner join multiple column to table one column

I have problems joining 2 table let say I have a column on the 1st table name nationalitycode(this is number) and it fetches the description(it could be american, chinese etc) depending on the first table nationalitycode. So I tried inner join the table so the first inner join is good and working perfectly.

here is the code: 

SELECT person.firstnm, person.middlenm, person.lastnm, refcd.description
FROM person
INNER JOIN refcd
ON person.natcd = refcd.id;

However when I try to join the 2nd column from the 1st table let say the column name is gencd(gendercode its a foreign key) but when I updated the query to inner join 2 column from one table getting the value from one column in the 2nd table I get this error. "Column ambigously defined" I get this error and I understand this my question is is there anyway to achieve my desired output? I want to join 2 table, specifically joining 2 column on table 1 to 1 column in table 2.

Here is my updated query:

SELECT person.firstnm, person.middlenm, person.lastnm, refcd.description
FROM person
INNER JOIN refcd
ON person.natcd = refcd.id
INNER JOIN refcd
ON person.gencd = refcd.id;

Help is really appreciated.

Upvotes: 0

Views: 2875

Answers (1)

Juzer
Juzer

Reputation: 33

Your query should be

SELECT person.firstnm, person.middlenm, person.lastnm, refcd.description
FROM person INNER JOIN refcd ON person.natcd = refcd.id
INNER JOIN refcd R2 ON person.gencd = R2.id;

Upvotes: 1

Related Questions