I have two tables, the transfer table and the clubs table.
This is the table transfer:
| id | name | Surname | From Club | Go to the club | price | from backnumber | Return number |
|---|---|---|---|---|---|---|---|
| 0 | Example 1 | Example 2 | EX3 | EX4 | Example 5 | Example 6 | Example 7 |
This is the table club:
| id | abbreviation | full name |
|---|---|---|
| 0 | EX3 | Example 3 |
| 1 | EX4 | Example 4 |
I want to get a row from the transfer table, get the fromclub, match it to the abbreviation in Clubs, get the full name from the row, and add it to the response. I want to do the same thing with the toclub column. Then I want to repeat for every other row in the table transfer.
To keep things simple, here is my expected result:
| id | name | Surname | From Club | Go to the club | price | from backnumber | Return number | From the full name of the club | toclubFull name |
|---|---|---|---|---|---|---|---|---|---|
| 0 | Example 1 | Example 2 | EX3 | EX4 | Example 5 | Example 6 | Example 7 | Example 3 | Example 4 |
I can't seem to find a way to build a query that does this without getting around 512 rows of results, even though I currently only have 8 rows of results.
SELECT * FROM transfers, (SELECT clubs.fullname FROM transfers, clubs WHERE clubs.abbr = transfers.fromclub) AS fromclubfullname, (SELECT clubs.fullname FROM transfers, clubs WHERE clubs.abbr = transfers.toclub) AS toclubfullname
It returns 512 rows and two columns (called fullname) instead of one column fromclubfullname and toclubfullname.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
You can do this via
left join-SELECT transfers.*, c1.fullname as fromclubfullname, c2.fullname as toclubfullname FROM `transfers` LEFT JOIN clubs c1 ON (c1.abbr = transfers.fromclub) LEFT JOIN clubs c2 ON (c2.abbr = transfers.toclub)