Show result from one table where ID's match on another?

[Kristoff1875]

Evening you clever lot on here!

 

I'm sure this won't take long for you guys to give me some sterling advice!

 

I have 2 tables, 1 which has matchday results on it, 1 which has player details on it. The ID's match up and tie in in the database, but on the page i'm on, I would like to display all the players that haven't got a value for this specific matchday. Important table layouts as follow:

 

MatchdayScore

 

PlayerID, MatchNumber

1234, 12

 

Players

 

PlayerID, PlayerName

1234, Player One

 

Each time a player plays a game, MatchNumber will have the relevant match number added for that player, so as you can see above, Player One (1234) has taken part in MatchNumber 12. I would like to add a search if possible to say something like:

 

Select * FROM Players WHERE PlayerID AND MatchNumber NOT IN MatchdayScore

 

So it only shows players who haven't got match 12 in MatchdayScore, but i'm not sure how i'd go about this?

 

Any advice massively appreciated.

 

Thanks

[Barand]

SELECT p.playerID, p.playerName

FROM players p

  LEFT JOIN MatchdayScore m

    ON p.playerID = m.playerID AND m.matchNumer = 12

WHERE m.playerID IS NULL

[Kristoff1875]

Can't edit, so need to repost, sorry...

Also need to search by ClubID from Players table if possible?

[Kristoff1875]

Many thanks Barand, made a few changes:

SELECT p.*
FROM Players p
  LEFT JOIN MatchdayScore m
    ON p.PlayerID = m.PlayerID AND m.MatchNumber = $matchday
WHERE m.PlayerID IS NULL AND ClubID = $ClubID

and works perfectly. Hope you're well buddy!