Using MAX() and three tables, possibly four, need help with what goes where now lol

0o0o0 · July 12, 2015

Ok i think im over my head, ive come to a brick wall, and usually when this happens a reply of the simpliest form comes back. I might be thinking to hard.. here it goes..

Table ONE

champshorses2014 stats will always stay the same.

Table TWO

champshorses is updated everyday from the source.

Table THREE

horsesintoday is updated daily three times a day.

basically I have created two SELECT statements and all im really trying to figure out is how to merge the two together properly

so the final out put would appear as so...

TRACK CHURCHILL RACE1 HORSE1 STAT STAT STAT ( and joined here) (same R1) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE2 STAT STAT STAT ( and joined here) (same R1)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE3 STAT STAT STAT ( and joined here) (same R1)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE4 STAT STAT STAT ( and joined here) (same R1)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE5 STAT STAT STAT ( and joined here) (same R1)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE1 STAT STAT STAT ( and joined here) (same R2)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE2 STAT STAT STAT ( and joined here) (sameR2)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE3 STAT STAT STAT ( and joined here) (sameR2)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE4 STAT STAT STAT ( and joined here) (sameR2)Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE5 STAT STAT STAT ( and joined here) (sameR2)Maxstat maxstat Maxstat

even tho all the MAX STATS at the ends are the same max numbers, of each Track, Race number group.. doesnt matter..

I need these merged to print out or return as above in single lines..

Basically im not knowledgeable of joining, left join, inner join or merge, or what is needed to mash these two select statements together as one.

I have

TRACK CHURCHILL RACE1 HORSE1 STAT STAT STAT

TRACK CHURCHILL RACE1 HORSE2 STAT STAT STAT

TRACK CHURCHILL RACE1 HORSE3 STAT STAT STAT

TRACK CHURCHILL RACE1 HORSE4 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE1 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE2 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE3 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE4 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE5 STAT STAT STAT

with the first code..

and

TRACK CHURCHILL RACE1 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE3 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE4 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE5 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE6 Maxstat maxstat Maxstat

with the second code..

need em mashed.. to appear like so...

TRACK CHURCHILL RACE1 HORSE1 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE2 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE3 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE4 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE1 HORSE5 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE1 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE2 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE3 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE4 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 HORSE5 STAT STAT STAT ( and joined here) Maxstat maxstat Maxstat

So Maxstats at ends will be the numbers in select statement two I posted below..

Actually it should appear like this when done...

TRACK CHURCHILL RACE1 HORSE1 STAT STAT STAT ( and joined here) 24 36 5000

TRACK CHURCHILL RACE1 HORSE2 STAT STAT STAT ( and joined here) 24 36 5000

TRACK CHURCHILL RACE1 HORSE3 STAT STAT STAT ( and joined here) 24 36 5000

TRACK CHURCHILL RACE1 HORSE4 STAT STAT STAT ( and joined here) 24 36 5000

TRACK CHURCHILL RACE1 HORSE5 STAT STAT STAT ( and joined here) 24 36 5000

even tho they are the same numbers.. the 24 could be from horse 1 first stat as highest, and 36 from horse 5 second stat highest, and horse 3 has the hiighest last stat, doesnt matter theur added to all the ends.. whatever works..

then in php I will just simply do a php if statement..

if ($t1.Track = $t4.Track and $t1.Race = $4t.Race And $t2.rank = $t4.maxrank)
$col14 = "#006600";

if ($t1.Track = $t4.Track and $t1.Race = $4t.Race And $t3.rank2014 = $t4.maxrank2014)
$col14 = "#006600";

etc.. through out.

something like that.. but probably dont need the t2.t3.t4. as they will be rank rank2014 and maxrank maxrank2014 anyhow..

dont worry about this this I can do later.

just need the two mashed lol.

Code 1

appears something like this when executed..

TRACK CHURCHILL RACE1 HORSE1 STAT STAT STAT

TRACK CHURCHILL RACE1 HORSE2 STAT STAT STAT

TRACK CHURCHILL RACE1 HORSE3 STAT STAT STAT

TRACK CHURCHILL RACE1 HORSE4 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE1 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE2 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE3 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE4 STAT STAT STAT

TRACK CHURCHILL RACE2 HORSE5 STAT STAT STAT

Select statement 1:

SELECT t1.Horse, t1.Track, t1.Race, t1.Jockey, t1.Trainer, t1.Owner, t1.Date, t2.speedFigure, t2.rank, t2.perStart, t2.topThree, t2.topThreePercentage,  t2.winPercentage, t2.starts, t2.win, t2.place, t2.show, t2.earnings, t3.speedFigure2014, t3.rank2014, t3.perStart2014, t3.topThree2014, t3.topThreePercentage2014,  t3.winPercentage2014, t3.starts2014, t3.win2014, t3.place2014, t3.show2014, t3.earnings2014, (t2.speedFigure + t3.speedFigure2014) AS figtote, (t2.rank + t3.rank2014) AS ranktote, (t2.perStart + t3.perStart2014) AS avgtote, (t2.topThree + t3.topThree2014) AS pronum, (t2.topThreePercentage + t3.topThreePercentage2014) AS pronumpercent FROM champsintoday as t1 LEFT JOIN champshorses as t2 ON t1.HorseRef = t2.referenceNumber LEFT JOIN champshorses2014 as t3 ON t1.HorseRef = t3.referenceNumber2014 WHERE t1.Date = CURDATE() ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC, t2.speedFigure DESC

Statement 2 this grabs only the maxs figures of all horses in each race at each track, single lines

like so..

TRACK CHURCHILL RACE1 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE2 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE3 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE4 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE5 Maxstat maxstat Maxstat

TRACK CHURCHILL RACE6 Maxstat maxstat Maxstat

Select statement two:

SELECT t1.Track, t1.Race, MAX(t2.speedFigure) AS maxf2015, MAX(t3.speedFigure2014) AS maxf2014, MIN(t2.rank) AS maxr2015, MIN(t3.rank2014) AS maxr2014, MAX(t2.perStart) AS maxa2015, MAX(t3.perStart2014) AS maxa2014, MAX(t2.speedFigure + t3.speedFigure2014) AS maxtoprating, MIN(t2.rank + t3.rank2014) AS maxtoprank, MAX(t2.perStart + t3.perStart2014) AS maxtopavg, MAX(t2.starts) as maxstarts, MAX(t2.winPercentage) as maxwinPercentage, MAX(t2.topThreePercentage) as maxtopThreePercentage, MAX(t2.win) as maxwin, MAX(t2.place) as maxplace, MAX(t2.show) as maxshow,MAX(t2.topThree) as maxtopThree,MAX(t2.earnings) as maxearnings, MAX(t3.starts2014) as maxstarts2014, MAX(t3.winPercentage2014) as maxwinPercentage2014, MAX(t3.topThreePercentage2014) as maxtopThreePercentage2014, MAX(t3.win2014) as maxwin2014, MAX(t3.place2014) as maxplace2014,MAX(t3.show2014) as maxshow2014, MAX(t3.topThree2014) as maxtopThree2014,MAX(t3.earnings2014) as maxearnings2014, MAX(t2.topThree + t3.topThree2014) AS maxpronum, MAX(t2.topThreePercentage + t3.topThreePercentage2014) AS maxpronumpercent,(t2.speedFigure + t3.speedFigure2014) AS maxfigtote, (t2.rank + t3.rank2014) AS maxranktote, (t2.perStart + t3.perStart2014) AS maxavgtote FROM champsintoday as t1 LEFT JOIN champshorses as t2 ON t1.HorseRef = t2.referenceNumber LEFT JOIN champshorses2014 as t3 ON t1.HorseRef = t3.referenceNumber2014 WHERE t1.Date = CURDATE() GROUP BY t1.Date, t1.track, t1.Race ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC, t2.speedFigure DESC

im just clueless as to what I need... join? left join? where to put brackets, or whatever is needed to make these two codes spit out single full figure lines. If I told ya how many attempts and weeks hours its taken to get these two together in one statement youd probably laugh.. lets just say every day for a few hours since june 22nd. lol.. meanwhile working around it and adding to it, but cannot for the life of me place the two together into one properly.

This is a personal hobby, not a job or commercial project, just basically mine that opens once in a browser and stays on my laptop for a day during the weekend.. So loading time, efficency or spaghetti coding is not an issue, and help is great as long as it works. lol! thanks.

Edited July 12, 2015 by 0o0o0

0o0o0 · July 12, 2015

I guess the "ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC, t2.speedFigure DESC" wont be needed in both. Is this simple? am i over looking it? Im worried about the replies already. Thanks guys.

Edited July 12, 2015 by 0o0o0

Barand · July 12, 2015

Instead of two history data tables (champhorses and champhorses2014) which have identical structure, combine and extract this year and previous year stats based on the date. However, as you currently have it you need a union of the two table to get the max values

Something like this

SELECT 
  t1.Horse
, t1.Track
, t1.Race
, t1.Jockey
, t1.Trainer
, t1.Owner
, t1.Date
, t2.perStart
, t2.rank
, t2.speedFigure
, t3.perStart2014
, t3.rank2014
, t3.speedFigure2014
, maxps
, maxrank
, maxsf
FROM champsintoday t1
LEFT JOIN champshorses t2 ON t1.HorseRef = t2.referenceNumber
LEFT JOIN champshorses2014 t3 ON t1.HorseRef = t3.referenceNumber2014
INNER JOIN 
	(
	SELECT referenceNumber
    , MAX(perStart) as maxps
	, MAX(rank) as maxrank
	, MAX(speedFigure) as maxsf
	FROM 
		(
		SELECT referenceNumber
        , perStart
        , rank
        , speedFigure
        FROM champshorses
        UNION 
        SELECT referenceNumber2014
        , perStart2014
        , rank2014
        , speedFigure2014
        FROM champshorses2014
        ) as bothtbls
	GROUP BY referenceNumber
    ) as t4 ON t1.HorseRef = t4.referenceNumber
WHERE t1.Date = CURDATE()
ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC, t2.speedFigure DESC

0o0o0 · July 13, 2015

Just getting select statement 2 to attach to select statement 1 by Track and Race.. Select statement 2 will be the same numbers for all horses in each race, as they are the max figures of the entire race. I just need need em to join together lol.. its there! its soo close and I can figure out the structure of the code to do so.

Select statement 1 ---> select statement 2 .. joined.. by CURDATE, Track, Race (basically the race number)

and the desired result appears like..