JOIN problem

MySQL & MariaDB Other MySQL® Questions
1

Hi all,
I am trying to write a query which does the following.

  1. There are two queries which gets results in random from two tables using UNION

This is how it looks like
(select i from tbl1)
UNION
(select i from tbl2)
ORDER BY RAND()
I want this result set to be random. But before displaying the results from these tables I want to display another set of results from table 3 which would be displayed first and then followed by the random results SET.

So I tried using like the following

(select tbl3.i from tbl3 LIMIT 3)
UNION
(
(select tbl1.i from tbl1)
UNION
(select tbl2.i from tbl2)
ORDER BY RAND()
)

I get an error. The problem comes when I am nesting the queries. I want the first query to execute first and then the second query which would display results in random from the two queries.

Is the above senario possible…

Here is an example on how I want it to work…

I want t3.1 results to be displayed first and then followed by combination of resuts from tbl1 and tbl2.

Thank you in advance.

2

Hi charanv,

to correct the syntax of your example (sry for other column and table names):
You have to select the result of the inner union like this:

SELECT i FROM (
SELECT i FROM t2
UNION
SELECT i FROM t3
ORDER BY RAND()
)t4

Than, you can merge the result as you want:

SELECT i FROM t1
UNION
SELECT i FROM (
SELECT i FROM t2
UNION
SELECT i FROM t3
ORDER BY RAND()
)t4

But I think this approach is really slow with large datasets. A possible solution to speed things up could be creating a temporary table and insert the result with a random sorting-weight like this:

create temporary table tx(
i int unsigned key,
s tinyint unsigned not null,
key(s)
);

now you run:
insert into tx select i, 0 from t1;
insert into tx select i, rand() * 254 + 1 from t2;
insert into tx select i, rand() * 254 + 1 from t3;

and now you can select the result quickly using the index “s”

select i from tx order by s;

the create + insert in a procedure and it should be fast )