CODEWITHSUNDEEP
mysqlsql
Silver
Silver

Reputation: 17

Multiple foreign keys on same column

I have following tables:

Table: user
Columns
- id
- username
- full_name

Table: pet
Columns
- id
- pet_name

Table: results
Columns
- id
- user_id_1
- user_id_2
- user_id_3
- pet_id
- date
- some_text

Can I make an SQL query witch will give me one row with: id, full_name, full_name, full_name, pet_name, date, some_text where the results.id is 3?

Upvotes: 1

Views: 84

Answers (2)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Try Following query :

SELECT A.id, B.full_name, C.full_name, D.full_name, E.pet_name, A.date, A.some_text
FROM RESULTS AS A 
LEFT OUTER JOIN USER AS B ON A.USER_ID1 = B.ID
LEFT OUTER JOIN USER AS C ON A.USER_ID2 = C.ID
LEFT OUTER JOIN USER AS D ON A.USER_ID3 = D.ID
LEFT OUTER JOIN PET AS E ON A.PET_ID = E.ID
WHERE A.id = 3

Upvotes: 0

Andomar
Andomar

Reputation: 238086

select  u1.full_name
,       u2.full_name
,       u3.full_name
,       p.pet_name
,       r.date
,       r.some_text
from    Results r
join    User u1
on      u1.id = r.user_id_1
join    User u2
on      u2.id = r.user_id_2
join    User u3
on      u3.id = r.user_id_3
join    pet p
on      p.id = r.pet_id
where   r.id = 3

Upvotes: 4

Related Questions