JOIN trouble

[anakadote]

Hi. I have tried multiple inner and LEFT joins and DISTINCT combinations, but none give me my desired result.

Two tables, users and items. Each user has multiple items. I want to select each user once and only one of their items, with an item chosen randomly, if possible.

Thanks in advance!

[Blulagoon]

Are you using MySQL, if so have you tried adding ORDER BY RAND(); to select the item randomly. I'd try something like this:

$q1 = "Select distinct user from users";
$result=mysql_query($q1);
while($row = mysql_fetch_array($result)) {
$tmp_user = $row['user'];

$q2 = "Select item from items where user='$tmp_user' order by rand(); limit 1";
$result2=mysql_query($q2);
$row2=mysql_fetch_array($result2);
$item = $row2['item'];

echo $tmp_user." has item ".$item."<br><br>";

}

Not necessarily the tidiest way to do it, but it should achieve what you are looking for. You may not need 'distinct' user if the user is only in the user table once.

HTH - Blu

[anakadote]

Thanks blulagoon, but this is actually how i am doing it now. I'm trying to find a way to do it all in one query as opposed to an initial query with inner queries in a loop.

I am using MySQL.