How to select, match and return values ​​in mysql query?

Question

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.

Answer 1

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)