Jump to content

Union


Manny
 Share

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

Edited by Manny
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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...