Solved

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

Posted on 2004-04-21
4
2,228 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
password_verify with prepared statements 10 35
how do i form the query to get different columns total count 13 42
PHP loop not working 4 54
MySQL  on Tomcat 8 23
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 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

8 Experts available now in Live!

Get 1:1 Help Now