Solved

MySQL Bug workaround... (Error: 1247: Every derived table must have it's own alias )

Posted on 2004-04-21
4
2,226 Views
Last Modified: 2012-05-04
Is there a workaround for the mysql bug where it cannot use operations on a, from an union operation, derived a table? I get the MySQL error Error: 1247: Every derived table must have it's own alias which is supposed to be a bug i MySQL. The union query as it selfs work without any problems.

Bug description: http://bugs.mysql.com/bug.php?id=2435
I want to execute:
SELECT
SUM(Wins) AS nbrOfWins,
SUM(Losses) AS nbrOfLosses,
SUM(Ties) AS nbrOfTies,
SUM(Score) AS totScore
FROM
(
(
SELECT
t1.teamname AS TeamName,
SUM(IF(homescore > visitorscore, 1,0)) AS Wins,
SUM(IF(homescore < visitorscore, 1,0)) AS Losses,
SUM(IF(homescore = visitorscore, 1,0)) AS Ties,
SUM(IF(homescore > visitorscore, 3, IF(visitorscore = homescore, 1, 0))) AS Score
FROM
games AS g
JOIN teams AS t1 ON homeid = t1.id
JOIN teams AS t2 ON visitorid = t2.id
WHERE
(g.homeid=t1.id AND g.visitorid=t2.id) AND
(t1.division='A' or t2.division='A') AND
(g.homescore IS NOT NULL AND g.visitorscore IS NOT NULL) AND
(g.homescore<>999 AND g.visitorscore<>999) AND
(g.homescore<>777 AND g.visitorscore<>777) AND
t1.id=1
GROUP BY t1.id
)UNION(
SELECT
t2.teamname AS TeamName,
SUM(IF(homescore < visitorscore, 1,0)) AS Wins,
SUM(IF(homescore > visitorscore, 1,0)) AS Losses,
SUM(IF(homescore = visitorscore, 1,0)) AS Ties,
SUM(IF(homescore < visitorscore, 3, IF(visitorscore = homescore, 1, 0))) AS Score
FROM
games AS g
JOIN teams AS t1 ON homeid = t1.id
JOIN teams AS t2 ON visitorid = t2.id
WHERE
(g.homeid=t1.id AND g.visitorid=t2.id) AND
(t1.division='A' or t2.division='A') AND
(g.homescore IS NOT NULL AND g.visitorscore IS NOT NULL) AND
(g.homescore<>999 AND g.visitorscore<>999) AND
(g.homescore<>777 AND g.visitorscore<>777) AND
t2.id=1
GROUP BY t2.id
)
)

//jan

P.S. This is an followup-question to http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_20951435.html that I found interesting D.S.
P.P.S.
DB-dump:
# phpMyAdmin SQL Dump
# version 2.5.3
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Apr 21, 2004 at 02:14 AM
# Server version: 4.1.1
# PHP Version: 4.3.4
#
# Database : `test`
#

# --------------------------------------------------------

#
# Table structure for table `games`
#

CREATE TABLE `games` (
  `gameid` int(11) NOT NULL auto_increment,
  `gamenum` int(11) NOT NULL default '0',
  `gamedate` datetime NOT NULL default '0000-00-00 00:00:00',
  `visitorid` int(11) NOT NULL default '0',
  `homeid` int(11) NOT NULL default '0',
  `visitorscore` int(11) NOT NULL default '0',
  `homescore` int(11) NOT NULL default '0',
  `fieldid` int(11) NOT NULL default '0',
  `modified` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`gameid`)
) TYPE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

#
# Dumping data for table `games`
#

INSERT INTO `games` VALUES (1, 1, '0000-00-00 00:00:00', 1, 2, 50, 70, 0, 0);
INSERT INTO `games` VALUES (2, 2, '0000-00-00 00:00:00', 2, 1, 31, 54, 0, 0);
INSERT INTO `games` VALUES (3, 3, '0000-00-00 00:00:00', 1, 2, 1, 1, 0, 0);
INSERT INTO `games` VALUES (4, 0, '0000-00-00 00:00:00', 2, 1, 50, 30, 0, 0);
INSERT INTO `games` VALUES (5, 5, '0000-00-00 00:00:00', 2, 1, 50, 60, 0, 0);

# --------------------------------------------------------

#
# Table structure for table `teams`
#

CREATE TABLE `teams` (
  `id` int(11) NOT NULL auto_increment,
  `teamname` varchar(20) NOT NULL default '',
  `division` char(1) NOT NULL default '',
  `points` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

#
# Dumping data for table `teams`
#

INSERT INTO `teams` VALUES (1, 'bandits', 'A', 0);
INSERT INTO `teams` VALUES (2, 'devils rays', 'A', 0);
D.D.S
0
Comment
Question by:madwax
  • 2
4 Comments
 
LVL 7

Author Comment

by:madwax
Comment Utility
found the solution myself by creating temporary tables....

I am going to ask to close this question.

//jan
0
 
LVL 7

Author Comment

by:madwax
Comment Utility
Workaround may be with the temporary table teamScores:

CREATE TEMPORARY TABLE teamScores
(
SELECT
t1.id AS teamId,
SUM(IF(homescore > visitorscore, 1,0)) AS Wins,
SUM(IF(homescore < visitorscore, 1,0)) AS Losses,
SUM(IF(homescore = visitorscore, 1,0)) AS Ties,
SUM(IF(homescore > visitorscore, 3, IF(visitorscore = homescore, 1, 0))) AS Score
FROM
games AS g
JOIN teams AS t1 ON homeid = t1.id
JOIN teams AS t2 ON visitorid = t2.id
WHERE
(g.homeid=t1.id AND g.visitorid=t2.id) AND
(t1.division='A' or t2.division='A') AND
(g.homescore IS NOT NULL AND g.visitorscore IS NOT NULL) AND
(g.homescore<>999 AND g.visitorscore<>999) AND
(g.homescore<>777 AND g.visitorscore<>777)
GROUP BY t1.id
)UNION(
SELECT
t2.id AS teamId,
SUM(IF(homescore < visitorscore, 1,0)) AS Wins,
SUM(IF(homescore > visitorscore, 1,0)) AS Losses,
SUM(IF(homescore = visitorscore, 1,0)) AS Ties,
SUM(IF(homescore < visitorscore, 3, IF(visitorscore = homescore, 1, 0))) AS Score
FROM
games AS g
JOIN teams AS t1 ON homeid = t1.id
JOIN teams AS t2 ON visitorid = t2.id
WHERE
(g.homeid=t1.id AND g.visitorid=t2.id) AND
(t1.division='A' or t2.division='A') AND
(g.homescore IS NOT NULL AND g.visitorscore IS NOT NULL) AND
(g.homescore<>999 AND g.visitorscore<>999) AND
(g.homescore<>777 AND g.visitorscore<>777)
GROUP BY t2.id
);

SELECT
teamid,
teamname,
SUM(Wins) AS nbrOfWins,
SUM(Ties) AS nbrOfTies,
SUM(Losses) AS nbrOfLosses,
SUM(Score) AS nbrOfPoints
FROM teamScores
JOIN teams ON teams.id = teamid
GROUP BY teamid
ORDER BY nbrOfPoints DESC


@Moderators:
Decide yourselves if you want to PAQ this question or not, my opinion is that it could be good to consider the question answered since the union-bug may be workedaround with the creation of a temporary table...

//madwax
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
Closed, 500 points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now