/    Sign up×
Community /Pin to ProfileBookmark

Sub Select MIN not workng

I have a complex query. It’s pulling football player data and filtering by a group of competitions. To achieve this I’m needing to join a bunch of tables. All ID’s are index columns. It works fine but I’m trying to get the players earliest game. It’s not working, it’s returning results. But the MM it’s returning a different matchID which has the exact same kickoff time.

I can’t post the code so here it is here
https://jsfiddle.net/cbfdhs1k/

to post a comment

18 Comments(s)

Copy linkTweet thisAlerts:
@sibertJan 28.2023 — What type of column is kickOff? Will there be exact the same values in different games?

Just curious, why do you use backtics for your column names? Not needed in some databases if there is no uppercase involved.

Copy linkTweet thisAlerts:
@kiwisauthorJan 28.2023 — kickOff is of type datetime(6) and yes there will be.

backtics, I'm not sure. Probably just a hangover from merging different queries.
Copy linkTweet thisAlerts:
@sibertJan 28.2023 — > @kiwis80#1650264 kickOff is of type datetime(6) and yes there will be.

If the values are equal, there may be a chance that you can get random results. Is there a way to make this unique?

(SELECT matchId FROM matches WHERE kickOff = MIN(m.kickOff) and gd.playerId=1)
Copy linkTweet thisAlerts:
@kiwisauthorJan 28.2023 — @sibert#1650265

just tried that, it’s just finding the first row with the oldest datetime not the right one.

Tried adding playerId into sub select with no effect at all.

I thought by select from match data first, it wouldn’t find any matches without the player in it
Copy linkTweet thisAlerts:
@NogDogJan 28.2023 — > @kiwis80#1650260 I'm trying to get the players earliest game

Seems like it might be simpler to just do:
[code=sql]
SELECT distinct gd.playerId, m.kickOff
FROM matchdata md
LEFT JOIN matches m ON m.matchId = md.matchId
LEFT JOIN roundmatches rm ON m.matchId= rm.matchId
LEFT JOIN rounds r ON r.roundId = rm.roundId
LEFT JOIN competitions c ON r.competitionId = c.competitionId
LEFT JOIN competitiongroupcomps cgc ON c.competitionId = cgc.competitionId
WHERE cgc.competitionGroupId = 45 AND gd.playerId = 6
ORDER BY m.kickOff LIMIT 1
[/code]
Copy linkTweet thisAlerts:
@kiwisauthorJan 28.2023 — @NogDog#1650267

Yeah but if I was to remove the playerId in the where clause and add it into the groupBy it won't work :-(
Copy linkTweet thisAlerts:
@NogDogJan 28.2023 — Difficult for me to answer without really understanding the database schema, but maybe all you need is something like:
[code=sql]
SELECT gd.playerId, MIN(m.kickOff) AS MM
FROM matchdata md
LEFT JOIN matches m ON m.matchId = md.matchId
LEFT JOIN roundmatches rm ON m.matchId= rm.matchId
LEFT JOIN rounds r ON r.roundId = rm.roundId
LEFT JOIN competitions c ON r.competitionId = c.competitionId
LEFT JOIN competitiongroupcomps cgc ON c.competitionId = cgc.competitionId
GROUP BY gd.playerid
[/code]

However, that still looks problematic to me, since (a) I don't see a table name alias of "gd" specified in any of the FROM/JOIN statements, so am not sure where playerid is actually coming from, and (b) if matches is left-joined, then it could end up null if there are no matches. (Should it be "inner join", instead?)
Copy linkTweet thisAlerts:
@kiwisauthorJan 28.2023 — @NogDog#1650279

Sorry gd should read md.

md table has playerId and matchId columns. Joining the match, the player and position etc tother.

The m match table has kick off time, venue, referee etc.

I can't get MIN(kickoff) as I want the earliest / first match ID not just the time
Copy linkTweet thisAlerts:
@kiwisauthorJan 28.2023 — MIN kick off time is correct, I try find the match ID against this and the ID relates to a game years later.

My count returns the right number of matches as well.

``<i>
</i>COUNT(m.gameId),
MIN(m.kickOff) as MinK,
(SELECT m.matchId WHERE MIN(m.kickOff) limit 1) as MM,<i>
</i>
``
Copy linkTweet thisAlerts:
@kiwisauthorJan 28.2023 — To isolate the issue, I've cut the query back and this still fails.

I have a matchdata table, which has matchId and playerId as indexes. The match table with matchId as index.

I'm trying to get things like total matches, total goals etc but ALSO first match ID and first match time. So getting all match data by player ID but then a sub query within that to find first match ID based on kick off time.

https://jsfiddle.net/8wgd49jf/
Copy linkTweet thisAlerts:
@jitbanerjeeJan 29.2023 — hiiiii
Copy linkTweet thisAlerts:
@sibertJan 29.2023 — > @kiwis80#1650282 I'm trying to get things like total matches, total goals etc but ALSO first match ID and first match time. So getting all match data by player ID but then a sub query within that to find first match ID based on kick off time.

As a first step I should get correct result from the sub query.
``<i>
</i>SELECT matchId
FROM matchdata
WHERE min(m.kickOff)
AND playerId = 936 ---&gt; Same table else join?<i>
</i>
``

And If I get an unique hit, I should continue with the rest

Copy linkTweet thisAlerts:
@kiwisauthorJan 29.2023 — That doesn't work, kickOff is in the match table and I'm selecting from matchData there.

Here's my table structure


> CREATE TABLE matches (

> matchId int(11) NOT NULL,

> team1Id int(11) NOT NULL,

> team2Id int(11) NOT NULL,

> team1Score int(11) NOT NULL DEFAULT '0',

> team2Score int(11) NOT NULL DEFAULT '0',

> kickOff datetime(6) NOT NULL,

> venueId int(11) NOT NULL DEFAULT '0',

> ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
>
> ALTER TABLE matches

> MODIFY matchId int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12561;

> COMMIT;
>
> CREATE TABLE matchdata (

> matchId int(11) NOT NULL,

> playerId int(11) NOT NULL,

> ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
>
> ALTER TABLE matchdata

> ADD KEY matchId (matchId),

> ADD KEY playerId (playerId);

> COMMIT;
Copy linkTweet thisAlerts:
@sibertJan 29.2023 — > @kiwis80#1650293 That doesn't work, kickOff is in the match table and I'm selecting from matchData there.

https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/128

Cannot get this to work. Please create a full fiddle with a few sample data. Edit this fiddle, update and paste the new link in a post.
Copy linkTweet thisAlerts:
@kiwisauthorJan 30.2023 — I have updated it with some sample code. The real code is thousands of rows.
Copy linkTweet thisAlerts:
@sibertJan 30.2023 — > @kiwis80#1650306 I have updated it with some sample code.

And the link?
Copy linkTweet thisAlerts:
@kiwisauthorJan 30.2023 — Do you want to PM. I can give you select only remote access for a bit, then you can post your findings for future users?
Copy linkTweet thisAlerts:
@sibertJan 30.2023 — > @kiwis80#1650315 Do you want to PM.

There is no PM in this forum. You can send a link via [email protected]

> I can give you select only remote access for a bit

I have to test your query live. I cannot find any problems using readonly.

> then you can post your findings for future users?

Of course...
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 3.29,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...