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

I have two tables, transfers and clubs.

This is the table transfers:

id	firstname	lastname	fromclub	toclub	forprice	frombacknumber	tobacknumber
0	Example1	Example2	EX3	EX4	Example5	Example6	Example7

This is the table clubs:

id	abbr	fullname
0	EX3	Example3
1	EX4	Example4

I want to take a row from the table transfers, get the fromclub, match it with the abbr in clubs, get the fullname from that row, and add it to the response. I want to do the same thing for the toclub column. Then I want to repeat it for every other row in the table transfers.

To simplify, here is my expected result:

id	firstname	lastname	fromclub	toclub	forprice	frombacknumber	tobacknumber	fromclubfullname	toclubfullname
0	Example1	Example2	EX3	EX4	Example5	Example6	Example7	Example3	Example4

I cannot seem to find a way to build a query that does this without getting about 512 rows of results even though I only have 8 at the moment.

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 a column fromclubfullname and toclubfullname.

CodePudding user response：

You can do this by 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)