how to get "4 each" from a, b, c and d using limit

[ingernet]

hi folks,

got a bit of sticky wicket here. i have the following tables:

FILES_TABLE
ft_id
ft_filename
ft_Uid
ft_descrip

USERS_TABLE
U_id
U_firstname
U_lastname

ultimately, i'm looking to query 4 random files from the FILES_TABLE for each user found in the USERS_TABLE. the end result should look something like this:

Frank Jones (Uid 3):
file1
file3
file5
file9

John Merle (Uid 2):
file2
file8
file7
file4

is there a subselect or something I can use here?

thanks bunches!
inger

 

[TonyGroves]

How about something like:
[tt]
SELECT
users_table.*,q2.ft_filename
FROM
users_table
JOIN
(
SELECT *
FROM
(SELECT * FROM files_table WHERE ft_uid=u.id)
q1
ORDER BY RAND()
LIMIT 4
) q2
ON ft_uid=u_id
[/tt]

 

[ingernet]

That's great, thanks!
Inger