CODEWITHSUNDEEP
mysqlsql
BajaBob
BajaBob

Reputation: 2853

Join tables to query for a random row

I have two tables, one contains data created by an individual user and that data is assigned a unique primary key. My second table contains ratings of the aforementioned data and contains the user that created the rating, the actual rating, and the ID of the data being rated.

Table one, [data][user][data_id]

Table two, [rating][user][data_id]

I want a query that gives me a random row from table one based on the pretense that table two contains no ratings of random row being returned.

I think this will require a join, but I keep getting stuck. Is this possible with a single query?

Upvotes: 1

Views: 78

Answers (2)

bhamby
bhamby

Reputation: 15450

This should do it. It may be a little faster than doing the COUNT method, depending on your tables:

SELECT *
FROM tableOne t1
LEFT JOIN tableTwo t2
  ON t2.user= t1.user
 AND t2.data_id = t1.data_id
WHERE t2.user IS NULL
ORDER BY RAND()
LIMIT 1

This assumes that your user column is not nullable.

Upvotes: 1

idmean
idmean

Reputation: 14875

I think this should do it:

SELECT * FROM tableOne 
WHERE (SELECT COUNT(*) FROM tableTwo WHERE tableTwo.data_id = tableOne.data_id) = 0
ORDER BY RAND()
LIMIT 1

Upvotes: 2

Related Questions