CODEWITHSUNDEEP
phpmysqlsqljoin
Jason
Jason

Reputation: 452

MySQL join query for more than 1 id

So i encountered this problem when I was specifying a query for 2 connected tables with more than 1 id.

Suppose there are 2 tables:

Table Competition

table Competition refers to table Player by the player.id

Table Player

The problem is I want to retrieve the p1 name, p2 name and p3 name from table Competition...

Upvotes: 1

Views: 107

Answers (1)

John Woo
John Woo

Reputation: 263693

join the table player thrice to get it's equivalent values,

SELECT  a.*,
        b.name Player1_Name,
        c.name Player2_Name,
        d.name Player3_Name
FROM    Competition a   
        INNER JOIN player b
            a.p1_ID = b.ID
        INNER JOIN player c
            a.p2_ID = c.ID
        INNER JOIN player d
            a.p3_ID = d.ID

If one of the columns in table competition is nullable, better use LEFT JOIN than INNER JOIN.

To fully gain knowledge about joins, kindly visit the link below:

Upvotes: 3

Related Questions