ansaurus

Question

How to extract random entries from multiple mysql tables directly from database?

Answer 1

A:

Have you considered something like

(SELECT * from table1) union (select * from table2) order by rand() limit 0,5

Hal 2010-07-07 11:14:51

Now that i think of it, i think you need to union all the entries in both tables and then use the order by rand() limit 5

Hal 2010-07-07 11:15:50

no this didn't work, only random entries from table 1 are showing up

Starx 2010-07-07 11:20:29

have you tried with the union? let me correct the code

Hal 2010-07-07 11:21:05

It is giving this error "The used SELECT statements have a different number of columns". The structures are different

Starx 2010-07-07 11:27:45

@Starx: Don't do `SELECT *`. Instead, select only the columns that you want in the result set: `SELECT field_1, field_2, ...`

Daniel Vassallo 2010-07-07 11:29:55

Hmmmmm... that's a whole different thing. Have you considered implementing a UDF that 1. selects a random value to pick the source table2. selects a random row from that same table ?

Hal 2010-07-07 11:29:58

Good point Daniel, assuming those rows from distinct tables have anything relevant in common. If that's the case, i agree.

Hal 2010-07-07 11:32:05

Answer 2

A:

Ok, I managed to solve it

SELECT field1 f1, feild2 f2, field3 f3  
FROM table1 
UNION 
SELECT afield1, afield2, afield3 
FROM table2 
ORDER BY rand( ) LIMIT 0 , 5

Starx 2010-07-07 12:23:12

Note that you cannot select a different number of fields when doing a `UNION` between tables. This would return an error: `The used SELECT statements have a different number of columns`. I fixed your answer since you marked it as accepted. Also note that it is considered good practice to enclose the two `SELECT` statements in parenthesis, just to resolve the ambiguous position of the `ORDER BY` and `LIMIT`.

Daniel Vassallo 2010-07-25 07:01:01

You are wrong Daniel, because if you can see the answer above I have only select 3 columns from each table, so the error you are giving is impossible to get. I think you should try the answer first before downvoting an answer on the basis of your blank thought. I am highly offended by this downvote and comment. I only placed this answer when It solved my question

Starx 2010-07-25 07:39:23

@Starx: I didn't downvote you. I simply fixed your answer. Check the revision history: http://stackoverflow.com/posts/3194606/revisions. You had two syntax errors. There was an extra comma after `f3`, and you had a fourth column in the second `SELECT` query... This was the original answer `SELECT field1 f1, feild2 f2, field3 f3, FROM table1 UNION SELECT afield1, afield2, afield3, afield4 FROM table2 ORDER BY rand( ) LIMIT 0 , 5`

Daniel Vassallo 2010-07-25 07:41:42

OMG Daniel, I apologize to you for misunderstanding you. I saw a working answer above and then your commented and I went crazy. I am sorry for being rude with you.

Starx 2010-07-25 12:47:30

No problem mate :)

Daniel Vassallo 2010-07-25 15:00:53

ansaurus

tags:

views:

answers:

How to extract random entries from multiple mysql tables directly from database?

related questions