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

Result
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


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


Sorry, you can't reply to this topic. It has been closed.

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.