CODEWITHSUNDEEP
sqlunion
jontyc
jontyc

Reputation: 3495

Aliasing derived table which is a union of two selects

I can't get the syntax right for aliasing the derived table correctly:

SELECT * FROM 
  (SELECT a.*, b.* 
    FROM a INNER JOIN b ON a.B_id = b.B_id
    WHERE a.flag IS NULL AND b.date < NOW()
  UNION
  SELECT a.*, b.* 
    FROM a INNER JOIN b ON a.B_id = b.B_id
    INNER JOIN c ON a.C_id = c.C_id
    WHERE a.flag IS NOT NULL AND c.date < NOW())
  AS t1
ORDER BY RAND() LIMIT 1

I'm getting a Duplicate column name of B_id. Any suggestions?

Upvotes: 5

Views: 29244

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

First, you could use UNION ALL instead of UNION. The two subqueries will have no common rows because of the excluding condtion on a.flag.

Another way you could write it, is:

SELECT a.*, b.* 
FROM a 
  INNER JOIN b 
    ON a.B_id = b.B_id
WHERE ( a.flag IS NULL 
      AND b.date < NOW()
      )
   OR
      ( a.flag IS NOT NULL 
      AND EXISTS
          ( SELECT *
            FROM c 
            WHERE a.C_id = c.C_id
              AND c.date < NOW()
          )
      )
ORDER BY RAND() 
LIMIT 1

Upvotes: 1

babbageclunk
babbageclunk

Reputation: 8731

The problem isn't the union, it's the select a.*, b.* in each of the inner select statements - since a and b both have B_id columns, that means you have two B_id cols in the result.

You can fix that by changing the selects to something like:

select a.*, b.col_1, b.col_2 -- repeat for columns of b you need

In general, I'd avoid using select table1.* in queries you're using from code (rather than just interactive queries). If someone adds a column to the table, various queries can suddenly stop working.

Upvotes: 7

Lamak
Lamak

Reputation: 70638

In your derived table, you are retrieving the column id that exists in table a and table b, so you need to choose one of them or give an alias to them:

SELECT * FROM 
  (SELECT a.*, b.[all columns except id] 
    FROM a INNER JOIN b ON a.B_id = b.B_id
    WHERE a.flag IS NULL AND b.date < NOW()
  UNION
  SELECT a.*, b.[all columns except id]  
    FROM a INNER JOIN b ON a.B_id = b.B_id
    INNER JOIN c ON a.C_id = c.C_id
    WHERE a.flag IS NOT NULL AND c.date < NOW())
  AS t1
ORDER BY RAND() LIMIT 1

Upvotes: 2

Related Questions