7

I need a UNION of two queries, each of them works separatly, but not together, I get error: duplicate column name zipcode_id, please help.

(SELECT * FROM
    ( (SELECT * FROM jobs AS j LEFT JOIN zipcode AS z ON z.zipcode_id=j.zipcode_id WHERE 1 AND source='student'
              ORDER BY postdate DESC LIMIT 20) ORDER BY search_order DESC ) 
s1)
    UNION ALL
(SELECT * FROM
        (  (SELECT * FROM jobs AS j LEFT JOIN zipcode AS z ON z.zipcode_id=j.zipcode_id WHERE 1 AND source='manager'
               ORDER BY postdate DESC LIMIT 30, 1000000) ORDER BY postdate DESC )
s2)
Bitmap
  • 12,402
  • 16
  • 64
  • 91
ickx
  • 93
  • 1
  • 4

2 Answers2

7

If you're actually using SELECT * then the zipcode_id column is in both the Jobs table and the Zipcode table. As the error message says, you can't have the two columns using the same name as you have it. Because you are using subqueries there would be no way for the SQL engine to understand what you meant if you referred to the duplicated column name. For example, what should the following SQL return?

SELECT num FROM (SELECT 1 AS num, 2 AS num) AS SQ

Using SELECT * is a pretty bad practice in any event.

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • "you can't have the two columns using the same name" -- really? Is the following not legal syntax, then? `SELECT SNO, SNO FROM S UNION SELECT SNO, SNO FROM S;` ?? It seems to me that it is legal SQL syntax to have duplicate column names in a `UNION`. Or have I misinterpreted what you mean by "can't have the two columns using the same name"? – onedaywhen Oct 06 '11 at 09:40
  • Sorry, I should have been more clear (I'll update my answer). The problem is that he's putting those columns in a subquery. – Tom H Oct 06 '11 at 13:30
4

You may need to use different alias names for each sub-query. This should work:

    (SELECT * FROM
    ( (SELECT j1.* FROM jobs AS j1 LEFT JOIN zipcode AS z1 ON z1.zipcode_id=j1.zipcode_id WHERE 1 AND source='student'
              ORDER BY postdate DESC LIMIT 20) ORDER BY search_order DESC ) s1) UNION ALL
(SELECT * FROM
        (  (SELECT j2.* FROM jobs AS j2 LEFT JOIN zipcode AS z2 ON z2.zipcode_id=j2.zipcode_id WHERE 1 AND source='manager'
               ORDER BY postdate DESC LIMIT 30, 1000000) ORDER BY postdate DESC )
s2)
jward1152
  • 56
  • 2