SQL, Select multiple values based on foreign key, return the values

Question

How to select each team member name in a query, pilot and copilot based on the two foreign keys in team table

Table: player

+----------------+-----------------+------+-----+---------+----------------+
| Field          | Type            | Null | Key | Default | Extra          |
+----------------+------------------------+-----+---------+----------------+
| id             | int(10) unsigned| NO   | PRI | NULL    | auto_increment | 
| name           | varchar(60)     | NO   |     | NULL    |                | 
| isPilot        | TINYINT(1)      | NO   |     | NULL    |                | 
| age            | int(4)          | NO   |     | NULL    |                | 
+----------------+-----------------+------+-----+---------+----------------+

table: team

+----------------+-----------------+------+-----+---------+----------------+
| Field          | Type            | Null | Key | Default | Extra          |
+----------------+-----------------+------+-----+---------+----------------+
| id             | int(10) unsigned| NO   | PRI | NULL    | auto_increment | 
| pilot_id       | int(10) unsigned| NO   | FK  | NULL    |                | 
| copilot_id     | int(10) unsigned| NO   | FK  | NULL    |                | 
+----------------+-----------------+------+-----+---------+----------------+

Is this a good database setup?

Might be a repost of SQL: Foreign Key With multiple child values , but can't apply it to mine

MCVE: https://www.db-fiddle.com/f/a2fCdy6RFqgReuL8FThhDP/2 Result should be something like

+----------------+-----------------+
| Pilot          | Co-Pilot        | 
+----------------+-----------------+
| player4Name    | player3Name     | 
| player2Name    | player1Name     |
+----------------+-----------------+

See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jan 19 '19 at 14:20
Try [db-fiddle](https://www.db-fiddle.com/f/eZMyp1ERM8g8B1CTVwLb6z/0). — wchiquito, Jan 19 '19 at 15:12

GMB · Answer 1 · 2019-01-19T15:20:51.340

1

It seems like you need two simple JOINs :

SELECT
    p.name AS pilot,
    cp.name AS copilot
FROM
    team AS t
    INNER JOIN player AS p on p.id = t.pilot_id
    INNER JOIN player AS cp on cp.id = t.copilot_id

edited Jan 19 '19 at 15:20

answered Jan 19 '19 at 14:53

GMB

216,147
25
84
135

If "INNER JOIN pilot" replaced with "INNER JOIN player" it works perfectly – Lucy Horvath Jan 19 '19 at 15:13

SKG · Accepted Answer · 2019-01-19T15:08:19.243

1

Try this :

select p1.name, p2.name from player p1
join team t1 on p1.id = t1.pilot_id
join player p2 on p2.id = t1.copilot_id;

edited Jan 19 '19 at 15:08

answered Jan 19 '19 at 14:54

SKG

164
1
2
19

SQL, Select multiple values based on foreign key, return the values

2 Answers2