-1

So, I have a table spiller with players and their id. 

id | navn
-------------
1  | John Doe
2  | Matoma
3  | Lemaitre
4  | Koan


Secondly i have a table spillerpar that puts players in pairs. This table consist of an Auto inc id and the foreign key of spiller id x 2.

id | spiller_id_fk_1 | spiller_id_fk_2
--------------------------------------
1  | 1               | 4
2  | 3               | 2

Im trying to display the values of 2 fk's along with the fk id. I cant figure out how to. Please help. 

select a.sid1, a.spiller1, b.sid2, spiller2
FROM
(Select 1_spillerpar.spiller_id_fk_1 as sid1, 1_spiller.navn as spiller1
From 1_spillerpar
Join 1_spiller
ON 1_spillerpar.spiller_id_fk_1 = 1_spiller.id) as a,
(Select 1_spillerpar.spiller_id_fk_2 as sid2, 1_spiller.navn as spiller2
From 1_spillerpar
Join 1_spiller
ON 1_spillerpar.spiller_id_fk_2 = 1_spiller.id) as b

EDIT

Desired output would look like:

id | spiller_id_fk_1 | navn    | spiller_id_fk_2 | navn
--------------------------------------------------------
1  | 1               | John Doe| 4               | Koan
2  | 3               | Lemaitre| 2               | Matoma
iontomet
  • 135
  • 1
  • 2
  • 11

2 Answers2

2
Select s1.*,s2.* from spillerpar x
Join spiller s1 on s1.id = spiller_id_fk_1 
Join spiller s2 on s2.id = spiller_id_fk_2

Spillerpar.id is redundant

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • This works! Allthough I dont fully understand what you did. Would it bother you to explain how it works or point me to somewhere to read? Im thinking of s1 and s2 not being any of my tables. – iontomet Sep 13 '15 at 11:17
  • s1 is an alias for spiller. So is s2 – Strawberry Sep 13 '15 at 14:42
0

Strawberry's answer works as expected I think. An alternative is:

SELECT sp.id as `pairId`, s1.*,s2.* FROM spillerpar sp, spiller s1, spiller s2
WHERE s1.id = sp.spiller_id_fk_1 AND s2.id = sp.spiller_id_fk_2

SQL fiddle here

(Edited fiddle to match fk names + added pairId)

urban
  • 5,392
  • 3
  • 19
  • 45