CODEWITHSUNDEEP
mysqlsql
user2786384
user2786384

Reputation:

One SQL statement for two Foreign Keys

I have two tables. The first table contains ID, First_Name and Last_Name.

The 2nd table contains two foreign key fields containing different ID's of the first table.

I want to be able to run a SQL query that gets reults of the 2nd table which then grabs the First_Name of each member based on the two different foreign keys.

How would I go about doing this?

Upvotes: 1

Views: 69

Answers (3)

anonymous
anonymous

Reputation: 1968

I think correct sql would be below one using OR condition in outer join or using union

SELECT t1.id,t1.name from table1 t1, table2 t2 WHERE t1.id1 = t2.id1
UNION
SELECT t1.id,t1.name from table1 t1, table2 t2 WHERE t1.id1 = t2.id0

SELECT t1.id, t1.name from table2 t2 LEFT OUTER JOIN table1 t2 ON t1.id = t2.id or t1.id1 = t2.id0

Upvotes: 0

Zainab
Zainab

Reputation: 25

Suppose the second table has fields as such

userid, supervisorid ( both referring to the Id column of the first table )

you may write join to get the value like this 

SELECT t2.*, ID, firstname, lastname FROM table 2 t2
LEFT OUTER JOIN table 1 t1  ON
t2.userid = t1.id  
OR t2.supervisorid = t1.id

Upvotes: 1

juergen d
juergen d

Reputation: 204766

select t2.*, t1a.firstname, t1b.firstname
from table2 t2
left join table1 t1a on t2.fk1 = t1a.id
left join table1 t1b on t2.fk2 = t1b.id

Upvotes: 4

Related Questions