Distinct problem

[overflo]

I have a table (tblphoto) with 3 fields. ID (pk), workerID (fk) and photo. The table can hold mutliple photos of each worker. What I need a query to do is to randomly choose only one photo for each worker.

 

$get = mysql_query("SELECT DISTINCT workerID, photo

              FROM tblphoto

    ORDER BY RAND()");

 

but it isn't getting only one row for each worker. I am guessing that it is getting the DISTINCT workerID, photo combination but they will all be distinct.

 

Any ideas will be greatly appreciated.

 

Cheers

[The Little Guy]

after RAND() place this:

 

LIMIT 1

[overflo]

That just gives me one result

[redarrow]

that rite 1 random photo u asked for from the three fields........

[overflo]

Yeah, I need one photo for each worker.

 

Have just got it working like this

 

$sql = mysql_query("SELECT ID

      FROM tblworker");

while(list($workerID)=mysql_fetch_row($sql)){

 

 

$get = mysql_query("SELECT photo

FROM tblphoto

WHERE workerID = '$workerID'

ORDER BY RAND() LIMIT 1");

list($workerID, $photo)=mysql_fetch_row($get);

 

 

}//end while

 

But if anyone has better idea please tell.

 

Cheers