Derived tables / alias error when doing a union

[mluken]

I am running version 4.1.1a of MySQL server, and I am running into a problem. The below SQL query causes an error, saying:

[MySQL][ODBC 3.51 Driver][mysqld-4.1.1a-alpha-nt]Every derived table must have it's own alias

I have looked at this query up and down and can't find anything wrong with it. I did find somewhere that there is possibly a bug with MySQL and this issue? But according to what I read, it had already been fixed in this version? Is the probem with my code, or is it a bug in the MySQL version that I have? Help!!!

SELECT 
T1.USR_ID, 
T1.USR_NM, 
T1.EMAIL_FRMT 
FROM 
( 
SELECT * FROM 
( 
SELECT DISTINCT 
INNER1.USR_ID, 
INNER1.USR_NM
FROM 
DB2_SPARKS_USRS INNER1, 
DB2_SPARKS_MATCHES INNER3
WHERE 
INNER3.USR_ID = 1879487720 AND 
INNER3.MATCH_USR_ID = INNER1.USR_ID 
) 
UNION 
( 
SELECT DISTINCT 
INNER1.USR_ID, 
INNER1.USR_NM
FROM 
DB2_SPARKS_USRS INNER1, 
DB2_SPARKS_MATCHES INNER3
WHERE 
INNER3.MATCH_USR_ID = 1879487720 AND 
INNER3.USR_ID = INNER1.USR_ID AND 
) 
) T1 
ORDER BY T1.USR_NM

 

[r937]

i think the error message is rather straightforward

SELECT 
T1.USR_ID, 
T1.USR_NM, 
T1.EMAIL_FRMT 
FROM 
( 
SELECT * FROM 
( 
SELECT DISTINCT 
INNER1.USR_ID, 
INNER1.USR_NM
FROM 
DB2_SPARKS_USRS INNER1, 
DB2_SPARKS_MATCHES INNER3
WHERE 
INNER3.USR_ID = 1879487720 AND 
INNER3.MATCH_USR_ID = INNER1.USR_ID 
) [COLOR=red][b]as curly[/b][/color]
UNION 
( 
SELECT DISTINCT 
INNER1.USR_ID, 
INNER1.USR_NM
FROM 
DB2_SPARKS_USRS INNER1, 
DB2_SPARKS_MATCHES INNER3
WHERE 
INNER3.MATCH_USR_ID = 1879487720 AND 
INNER3.USR_ID = INNER1.USR_ID AND 
) [COLOR=red][b]as larry[/b][/color]
) T1
ORDER BY T1.USR_NM

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[mluken]

Ah, but looks can be deceiving! I thought that also, however, it doesn't like the "curly" alias in this case, giving me the error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ') as larry ) T1 ORDER BY T1.USR_NM' at line 1

Ah I need to have this fixed today too! Not looking good!

 

[r937]

okay, let's see...

taking a closer look at your union query, i see DISTINCT in both halves, which would be unnecessary since UNION does this automatically (UNION ALL keeps dupes)

and then i notice that your outermost query is attempting to draw a column (EMAIL_FRMT) that doesn't even exist in the inner query

and then i notice that both halves are drawing the same columns from the same two tables!

okay, so i'm willing to bet that you actually don't need DISTINCT, either (unless you've got multiple user names per user id)

try this --

select INNER1.USR_ID
     , INNER1.USR_NM
     , INNER1.EMAIL_FRMT 
  from INNER1.USR_ID
     , INNER1.USR_NM
  from DB2_SPARKS_USRS INNER1
     , DB2_SPARKS_MATCHES INNER3
 where INNER1.USR_ID 
       in ( INNER3.MATCH_USR_ID
          , INNER3.USR_ID )
   and INNER3.USR_ID = 1879487720 
order 
    by INNER1.USR_NM

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[mluken]

I didn't know that UNION would remove duplicates. That is good to know, so I have removed that. The EMAIL_FRMT column does exist... I just simplified the query slightly to post it and neglected to remove it there.... If you look a little closer, ytou will see that the 2 halves are slightly different (although that difference is very important:

SELECT 
T1.USR_ID, 
T1.USR_NM
FROM 
( 
SELECT * FROM 
( 
SELECT
INNER1.USR_ID, 
INNER1.USR_NM
FROM 
DB2_SPARKS_USRS INNER1, 
DB2_SPARKS_MATCHES INNER3
WHERE 
[b]
INNER3.USR_ID = 1879487720 AND 
INNER3.MATCH_USR_ID = INNER1.USR_ID
[/b]
) 
UNION 
( 
SELECT
INNER1.USR_ID, 
INNER1.USR_NM
FROM 
DB2_SPARKS_USRS INNER1, 
DB2_SPARKS_MATCHES INNER3
WHERE 
[b]
INNER3.MATCH_USR_ID = 1879487720 AND 
INNER3.USR_ID = INNER1.USR_ID AND 
[/b]
) 
) T1 
ORDER BY T1.USR_NM

Why in the world doesn't the union work like this?

 

[r937]

yes, i looked a little closer

did you look a little closer at my query?

you'll find it does the same thing as your union





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)