Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-04-21
4
Medium Priority
?
2,238 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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 series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

927 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