Mysql ORDER BY RAND() LIMIT 1 problem

The problem is the bellow query.

   SELECT RSV.Value
     FROM tblsubmitedservice SS
LEFT JOIN (SELECT ServiceID, Value 
             FROM tblservicevalue  
         ORDER BY RAND() 
            LIMIT 1) RSV ON RSV.ServiceID = SS.ServiceID

This query must retrieve 1 random value from tblservicevalue in JOIN with tblsubmitedservice like above. But sometimes (I do not know why someti开发者_StackOverflow中文版mes) the query return null. If I move the "LIMIT 1" to the end of query (no more inside the subquery), the query run correctly.

This query is simplified to understand and in the original query this solution is not possible.

May be all the ServiceID's in tblservicevalue don't have corresponding ServiceIDs in tblsubmittedservice i.e., there is not a strict one-to-one relation between the tables.

You can check the tables using this:

1>Check the number of rows of tblservicevalue and tblsubmittedservice are equal. 2>Next check if

SELECT
    RSV.Value
FROM tblsubmitedservice SS
    LEFT JOIN (SELECT ServiceID, Value FROM tblservicevalue) RSV ON RSV.ServiceID=SS.ServiceID

has the same no. of rows as tblservicevalue,tblsubmittedservice.

If either of 1,2 fail, clearly the behaviour is due to the reason I explained above.

Check if below query returns any rows:

 SELECT RSV.Value
 FROM tblsubmitedservice SS
 LEFT JOIN tblservicevalue RSV ON RSV.ServiceID = SS.ServiceID
 WHERE RSV.ServiceID IS NULL

If it does return any rows it means that some rows from tblsubmitedservice have no correspoding rows in tblservicevalue (with regard to ServiceID field). In case of LEFT JOIN if rows on the right side can't be found (ie. there's no such RSV.ServiceID in RSV table) NULLs are used instead.

Contrary to previous comment number of rows does not have to be equal.