Skip navigation
LouieCypher
Currently Being Moderated

Rugby League table nightmare please help!!

Aug 6, 2009 2:37 AM

OK I'm sure this has been done before but after much web searching I'm totally stuck.

 

I have been asked to create league for the local Rugby Club so created two tables:

 

LeagueTeam:

 

LeagueTeamsID int(11) PRIMARY
TeamName varchar (40)
Season int(11)

 


LeagueResult:

 

ResID int(11) PRIMARY
LeagueID int(11)
SeasonID int(11)
hteam int(11)
ateam int(11)
hscore varchar(3)
ascore varchar (3)

 

I have looked around the internet and found the following Query which seems to do what I want but, not being too good with MySQL queries I need a lot of help

 


SELECT TeamName AS Team,SUM(P) AS P,SUM(W) AS W,SUM(D) AS D,SUM(L) AS L,
SUM(F) AS F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts
FROM(
SELECT
hteam LeagueTeam,
1 P,
IF(hscore > ascore,1,0) W,
IF(hscore = ascore,1,0) D,
IF(hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE WHEN hscore > ascore THEN 2 WHEN hscore = ascore THEN 1 ELSE 0 END PTS
FROM LeagueResult
UNION ALL
SELECT
ateam,
1,
IF(hscore < ascore,1,0),
IF(hscore = ascore,1,0),
IF(hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE WHEN hscore < ascore THEN 2 WHEN hscore = ascore THEN 1 ELSE 0 END
FROM LeagueResult
) as tot
JOIN LeagueTeam ON tot.LeagueTeam=LeagueTeamsID
GROUP BY Team
ORDER BY SUM(Pts) DESC, SUM(GD) DESC

 


When I run it in DreamweaverCS3 I get a #1111 invalid use of group function.

 

When I run it in phpmyadmin the error message occures on  the line starting  " SELECT ateam, 1, "

 


Please can anybody help me to get this working in Dreamweaver or suggest another way of doing it?

 
Replies
  • Currently Being Moderated
    Aug 6, 2009 9:54 AM   in reply to LouieCypher

    First, I see an error with SUM(Pts) as you have defined it as uppercase PTS in your derived table. I would also suggest that you use a different alias for your summed values than their original values. For example, use something like SUM(D) AS Sum_D.That probably isn't causing the query to fail, but it is ambiguous.

     

    If you are still having problems, I would start by testing the derived table query to make sure it does not contain any syntax errors.

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 7, 2009 2:40 PM   in reply to LouieCypher

    >as I was saying I have been told it may be something

    >to do with the version of PHP not allowing sub selections??

     

    OK, first it would have nothing to do with the verison of PHP. Possibly they meant MySQL. This is not a sub-query it is more commonly known as a derived table.  AFAIK, MySQL started support of derived tables in 4.1. What version are you running? I have also heard that there are a lot of problems with derived tables in MySQL so you just might want to use a view instead.

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 9, 2009 11:52 AM   in reply to LouieCypher

    VIEW are just objects in sql that are based on select statements and can be used in queries just like tables. AFAIK, views were not supported in MySQL 4.1. That's a pretty old version with very limited sql functionality. Is there any way you can have it upgraded to new version?

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 10, 2009 10:44 PM   in reply to LouieCypher

    I don't know if this would be cheating, but have you tried setting it up in Access and then viewing the query as an SQL view.  You can cut and paste this into your work then and the difficult work is done for you.

     

    Alternatively, you could change codes to Union.  I am sure that would be a lot easier. 

     

    If you dont have a version of Access, let me know.  I might be able to throw something together for you in there.  I dont have MySQL as I tend to go down the Access and asp route I am afraid so can't really help with all the other stuff.  I have a book called Dreamweaver Demystified which I find really good.  Mine is the MX 2004 version but I am sure that Adobe will have sorted out a CS3 or CS4 version as well.

     

    Good luck with it

     

    Simon

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 11, 2009 6:40 AM   in reply to LouieCypher

    Possibly DW doesn't like you using the alias 'Team' in the group by. Try using the column name instead.

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 11, 2009 9:26 AM   in reply to LouieCypher

    Not really sure why DW is balking. Some things to try

     

    1) Make sure you have renamed PTS in the derived table so it is consistent with the naming in the outer query.

    2) Don't use alias names that are the same as column names. Rename them!

     

    If neither of those is the issue, I would go ahead and turn the derived table into a View. It will be easier to read and manage the SQL and make troubleshooting easier.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points