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
Subject
Written By
Posted
Select from matchdata with sub query
January 28, 2023 08:14PM
February 03, 2023 09:08PM
February 06, 2023 08:27AM
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.