Jump to content

Union


Manny

Recommended Posts

I have made a query which creates a league table based upon points specified in another table. Below is just a snippet with 3 members, but the actual source code has more than 50 members. Is there any way to cut down this code or am I doing it as I'm supposed to? Also, is there a maximum number of union statements you can make in the query?

(SELECT `ID_MEMBER`, `memberName`, SUM(`Perfect`) AS `Perfect`, SUM(`Outcome`) AS `Outcome`, SUM(`Goalscorer`) AS `Goalscorer`, (((SUM(`Perfect`) * 3) + SUM(`Outcome`)) + (SUM(`Goalscorer`) * 5)) AS `Points` FROM `prediction_league_predictions` WHERE `memberName` = 'Member1')UNION(SELECT `ID_MEMBER`, `memberName`, SUM(`Perfect`) AS `Perfect`, SUM(`Outcome`) AS `Outcome`, SUM(`Goalscorer`) AS `Goalscorer`, (((SUM(`Perfect`) * 3) + SUM(`Outcome`)) + (SUM(`Goalscorer`) * 5)) AS `Points` FROM `prediction_league_predictions` WHERE `memberName` = 'Member2')UNION(SELECT `ID_MEMBER`, `memberName`, SUM(`Perfect`) AS `Perfect`, SUM(`Outcome`) AS `Outcome`, SUM(`Goalscorer`) AS `Goalscorer`, (((SUM(`Perfect`) * 3) + SUM(`Outcome`)) + (SUM(`Goalscorer`) * 5)) AS `Points` FROM `prediction_league_predictions` WHERE `memberName` = 'Member3')ORDER BY `Points` DESC, `Perfect` DESC, `Outcome` DESC, `Goalscorer` DESC, `memberName` ASC

Link to comment
Share on other sites

That's what GROUP BY is for, you can group all of those fields by member name.

is there a maximum number of union statements you can make in the query?
That's generally not a great question to ask, if you're worried about hitting the limit of unions you're probably doing things the wrong way. I doubt there's a limit on the number of unions, but there's probably a limit as to how large a query can be (in fact, I know there is, because I've hit it).
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...