search

Home  >  Q&A  >  body text

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

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粉986028039P粉986028039231 days ago481

reply all(1)I'll reply

  • P粉827121558

    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)

    reply
    0
  • Cancelreply