An aliasing view that is a union of two samples

Question

I cannot get the syntax for the correct overlay of the view:

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 get a duplicate column name B_id. Any suggestions?

Answer 1

The problem is not union , it select a.*, b.* In each of the internal select statements, since a and b have B_id columns, which means that you have two B_id columns as a result.

You can fix this by changing the selection to something like:

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

In general, I would avoid using select table1.* In queries that you use from code (and not just for interactive queries). If someone adds a column to the table, various queries may suddenly stop working.

Answer 2

In your view, you retrieve the column identifier that exists in table a and table b, so you need to select one of them or give them an alias:

 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 

Answer 3

First you can use UNION ALL instead of UNION . The two subqueries will not have common rows due to the exclusion of the condition on a.flag .

Another way you could write 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