MySQL Forums
Forum List  »  Newbie

Select from matchdata with sub query
Posted by: B R
Date: January 28, 2023 08:14PM

I have a table matchData which has matchId and playerId as indexes. I also have match table which has a matchId as a index.

I’m trying to find all matchData records for a playerId but also getting the matchId of their first match. In the match table I have a datetime column called kickOff.

This returns data but is finding the wrong matchId

SELECT md.playerId, COUNT(md.matchId),
MIN(m.kickOff) as MinK,
MAX(m.kickOff) as MaxK,
(SELECT `matchId` FROM `matchdata` where min(m.kickOff) limit 1) as M,
(SELECT `matchId` FROM `matchdata` ORDER BY m.kickOff ASC limit 1) as MM,
(SELECT `matchId` FROM matches WHERE `kickOff` = min(m.kickOff) limit 1) as Mmm,
(SELECT `matchId` FROM matches WHERE `kickOff` = MAX(m.kickOff) limit 1) as MX

FROM matchdata md
LEFT JOIN matches As g ON m.matchId = md.matchId
WHERE md.playerId = 936
Group By md.playerId

Options: ReplyQuote

Written By
Select from matchdata with sub query
January 28, 2023 08:14PM
February 03, 2023 09:08PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.