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.
P粉8271215582024-04-03 15:15:27
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)