Solved

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

Posted on 2004-04-21
4
2,236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

622 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