CODEWITHSUNDEEP
mysqlsubquery
christian
christian

Reputation: 2479

MYSQL, Subquery Reference in Union

Is there any way to reference a subquery in a union?

I am trying to do something like the following, and would like to avoid a temporary table, but the subquery will be drawn from a much larger dataset so it makes sense to only do it once..

SELECT * FROM (SELECT * FROM ads WHERE state='FL' AND city='Maitland' AND page='home' ORDER BY RAND()) AS sq WHERE spot = 'full-banner' LIMIT 1
UNION
SELECT * FROM sq WHERE spot = 'leaderboard' LIMIT 1
UNION
SELECT * FROM sq WHERE spot = 'rectangle1' LIMIT 1
UNION
SELECT * FROM sq WHERE spot = 'rectangle2' LIMIT 1
.... etc,,

It's a shame that DISTINCT can't be specified for a single column of a result set.

Upvotes: 1

Views: 576

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Well, there is no way to do what you're trying to do without repeating the creation of the derived table.

If querying ads is really expensive then you should try adding an index like:

alter table ads add index (state, city, page, spot);

If after adding that index the query takes too much, then I'd recommend creating a table to store this data and then query that table for each spot.

Depending on your data, you could play around with GROUP BY to get similar results.

Upvotes: 1

Related Questions