5

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?

jontyc
  • 3,445
  • 6
  • 29
  • 36

3 Answers3

7

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.

babbageclunk
  • 8,523
  • 1
  • 33
  • 37
  • What confuses me is if I remove the outer SELECT and the alias, I get a record set returned without the duplicate column error. – jontyc Nov 29 '11 at 13:52
  • 2
    @jontyc - If you remove the outer select, then you aren't using the derived table as a table, its a simple `SELECT` and as such, it doesn't need to have different column names, you can `SELECT` as many repeated columns as you want – Lamak Nov 29 '11 at 13:57
2

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
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • This answer is confusing. `[all columns except id]` is not valid SQL syntax. A valid example of how to alias the duplicate column name (without having to list all the other column names) might have been useful. – Jake Nov 09 '19 at 00:11
1

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
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • I actually got the sql from another question I asked at http://stackoverflow.com/questions/8309390/joins-based-on-conditions-in-multiple-tables. I won't have a chance to test it until tomorrow but it looks better. If you'd like to post it over there, I'll reconsider the answer I selected. – jontyc Nov 29 '11 at 14:09
  • I have indeed chosen your rewrite. – jontyc Nov 30 '11 at 00:31
  • @jontyc: Read this article too, because `ORDER BY RAND()` is slow: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ – ypercubeᵀᴹ Nov 30 '11 at 00:37
  • And this: http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function – ypercubeᵀᴹ Nov 30 '11 at 00:40
  • And this: http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql/213242#213242 – ypercubeᵀᴹ Nov 30 '11 at 00:40
  • Selecting a random row - and fast - is far from trivial. – ypercubeᵀᴹ Nov 30 '11 at 00:40
  • Agreed, I had a big think about this earlier and went with ORDER BY RAND() LIMIT 1 because of the small number of records I will be selecting from (typically 10-100) and ample capacity of the system. – jontyc Nov 30 '11 at 06:13