Aliasing derived table which is a union of two selects

Question

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?

Answer 1

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.