Hi Guru's
I have two mysql queries that return a bunch of different records and figures. but I want to multiply one figure from one query by another fingure from the second query. I'm sure it can't be that hard, but this is a first for me so any help would be massively appreciated!
Here are the two queries: (they return exactely what I need, I'll post a follow up showing the output of each...)
$top40 = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points AS Venue_Points, SubSelect.count_results AS Venue_Play_Count, SubSelect3.sum_points3 AS Total_Points
FROM Player, (
SELECT Player1.MembershipNo, Venue1.VenueName, SUM( Position1.Points ) AS sum_points, COUNT( Player1.MembershipNo ) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
AND Results1.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player1.MembershipNo, Venue1.VenueName)SubSelect, (
SELECT Player3.MembershipNo, SUM( Position3.Points ) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
AND Results3.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player3.MembershipNo)SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points = (
SELECT MAX( SubSelect1.sum_points2 )
FROM (
SELECT Player2.MembershipNo, Venue2.VenueName, SUM( Position2.Points ) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
AND Results2.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player2.MembershipNo, Venue2.VenueName ) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >=25
ORDER BY SubSelect.sum_points DESC
LIMIT 0 , 40";
$result = mysql_query($top40) or die("Couldn't execute query because: ".mysql_error());
$AvePlayers = "SELECT Venue.VenueName, COUNT( Results.MembershipNo ) , COUNT( DISTINCT Results.Date ) , cast( coalesce( COUNT( Results.MembershipNo ) / COUNT( DISTINCT Results.Date ) ) AS decimal( 10, 1 ) ) AS 'Average'
FROM Position, Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Results.Position = Position.Position
AND Venue.VenueID = Results.VenueID
AND Results.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Venue.VenueName
ORDER BY Average DESC
LIMIT 0 , 30";
$result2 = mysql_query($AvePlayers) or die("Couldn't execute query because: ".mysql_error());
