multiple sql joins

dakey

Hi, i have the below sql which is currently working fine

SELECT scoresLeague.member_id, SUM(scoresLeague.cp) AS totalCp, SUM(scoresLeague.week_score) AS totalScore, members.firstname, members.lastname, members.id FROM `members` LEFT JOIN scoresLeague on members.id = scoresLeague.member_id WHERE members.active = '1' LEFT JOIN GROUP BY scoresLeague.member_id ORDER BY totalScore DESC, totalCp DESC

But i now need to add to it which is where i am getting stuck, i somehow need to incorporate the following into it

SELECT SUM(score) AS gWinnerScore FROM `groupWinnerPrediction` WHERE memberId = '$row[id]'

Then to make it even harder i need to add totalScore and gWinnerScore together within the sql

any help greatly appreciated

dakey

sorry just noticed there was a typo in my original query, was meant to say this

SELECT scoresLeague.member_id, SUM(scoresLeague.cp) AS totalCp, SUM(scoresLeague.week_score) AS totalScore, members.firstname, members.lastname, members.id FROM `members` LEFT JOIN scoresLeague on members.id = scoresLeague.member_id WHERE members.active = '1' GROUP BY scoresLeague.member_id ORDER BY totalScore DESC, totalCp DESC

Crowly

Not sure if its the most effective way of doing it (or if it works as expected), but you could try with a subquery.
Also updated it to use aliases on the tables, less text.

SELECT sl.member_id, SUM(sl.cp) AS totalCp, SUM(sl.week_score) AS totalScore, 
m.firstname, m.lastname, m.id,
(SELECT SUM(g.score) FROM `groupWinnerPrediction` AS g WHERE g.memberId = '$row[id]') AS gWinnerScore
FROM `members` AS m 
LEFT JOIN scoresLeague AS sl ON m.id = sl.member_id 
WHERE m.active = '1' 
GROUP BY sl.member_id
ORDER BY totalScore DESC, totalCp DESC

Then to make it even harder i need to add totalScore and gWinnerScore together within the sql

Why can't you do this in php?

You should be able to do this, but i hope there is a better way ...

SELECT sl.member_id, SUM(sl.cp) AS totalCp, SUM(sl.week_score) AS totalScore, 
m.firstname, m.lastname, m.id,
(SELECT SUM(g.score) FROM `groupWinnerPrediction` AS g WHERE g.memberId = '$row[id]') AS gWinnerScore,
(SELECT SUM(gw.score) FROM `groupWinnerPrediction`AS gw WHERE gw.memberId = '$row[id]')+SUM(sl.week_score) AS totalx
FROM `members` AS m 
LEFT JOIN scoresLeague AS sl ON m.id = sl.member_id 
WHERE m.active = '1' 
GROUP BY sl.member_id
ORDER BY totalScore DESC, totalCp DESC

dakey

sorry that didn't work i just ran the sql directly in phpmyadmin and the gWinnerScore and totalx all show null ??

i would add them in php but i need to show this in a ordered table, therefore to do in php i would need to create an array or something which is beyond me.

dakey

sorry my fault, got it working. thanks