Subselect wierdness

[azzazzello]

I am trying to get 3 active article IDs from the table ARTICLES for a random active feed from table FEEDS. Here is the query I have:

select AID, a.fid as FID from ARTICLES a where active='Y' and a.fid = (SELECT f.fid FROM FEEDS f where active='Y' ORDER BY RAND() desc limit 1) limit 3;

However, the 3 articles I get do NOT all have the same FID.
The subselect query by itself does what it's supposed to - gives me a random number. So this query works

SELECT f.fid FROM FEEDS f where active='Y' ORDER BY RAND() desc limit 1

But in the context of the above subquery, wierd results are produced. Furthermore, if I remove the limit 3 from the subquery, I get a VARIABLE number of articles back. Help?

 

[azzazzello]

Hrm...just read the documentation closer

"Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed."

That explains it...I can't see a way around that to do what I need other than split up the queries, unless someone has an idea

 

[TonyGroves]

Looks like a temporary table is in order here.