Solved

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

Posted on 2004-04-21
4
2,230 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
ID: 10882985
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
ID: 10883239
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
ID: 10911646
Closed, 500 points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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