CODEWITHSUNDEEP
mysql
sark9012
sark9012

Reputation: 5747

The design of a query

SELECT u.username,
       r.position,
       r.score,
       r.winner,
       t.team
FROM   ".TBL_FOOT_TOUR_ROUNDS." r
       LEFT JOIN ".TBL_USERS." u
         ON u.id = r.winner
       LEFT JOIN ".TBL_FOOT_TOUR_PLAYERS." pl
         ON pl.userid = r.winner
       LEFT JOIN ".TBL_FOOT_TEAMS." t
         ON t.id = pl.team
WHERE  pl.tourid = '$tour_id' && r.tourid = '$tour_id' && r.round = '$i'
ORDER  BY r.position

I have one problem with this query. The WHERE pl.tourid = '$tour_id' is reliant on the LEFT JOIN ".TBL_FOOT_TOUR_PLAYERS." pl ON pl.userid = r.winner. As it's a left join, how can I make that WHERE only function if the LEFT JOIN does?

Can't think of a solution!

Thanks

Upvotes: 0

Views: 40

Answers (2)

davogotland
davogotland

Reputation: 2777

SELECT u.username,
       r.position,
       r.score,
       r.winner,
       t.team
FROM   ".TBL_FOOT_TOUR_ROUNDS." r
       LEFT JOIN ".TBL_USERS." u
         ON u.id = r.winner
       LEFT JOIN ".TBL_FOOT_TOUR_PLAYERS." pl
         ON pl.userid = r.winner
         AND pl.tourid = '$tour_id'
         AND r.tourid = '$tour_id'
         AND r.round = '$i'
       LEFT JOIN ".TBL_FOOT_TEAMS." t
         ON t.id = pl.team
ORDER  BY r.position

Upvotes: 0

David M
David M

Reputation: 72920

I guess that you only want rows with no matching winner, or where the winner has the specified tourid. In this case, you would use:

WHERE (pl.tourid IS NULL OR pl.tourid = '$tour_id')

Alternatively, if you only want to link to the player if (s)he has the right tourid, then add it to the ON clause:

ON pl.userid = r.winner AND pl.tourid = '$tour_id'

The results will be different, either might be what you are looking for.

Upvotes: 2

Related Questions