CODEWITHSUNDEEP
mysqlrandomforeign-keys
Sumithran
Sumithran

Reputation: 6565

Selcting Random queries from MySQL table

Am trying to SELECT a specific number of Random questions from the table

Something like 3 random questions from questions table which are tagged as group_1 and two from group_3

This is my SQL command

SELECT
    questions.qid,
    tags.tag
FROM
    tag_dir tag_dir
JOIN questions ON questions.qid = tag_dir.qid
JOIN tags ON tags.id = tag_dir.tag_id
WHERE tags.id having 1 and 2
LIMIT 0,10

But I don't know how to get a specific number of Random results

Here is my table structure image description here

Here is my db-fiddle

Upvotes: 4

Views: 105

Answers (2)

Sheikh Azad
Sheikh Azad

Reputation: 353

This might help if you can use UNIONs to get the random result set for each tag and then relate the other tables.

SELECT
    questions.qid,
    tags.tag
FROM
    (
        (SELECT * from tag_dir where tag_id=1 order by rand() LIMIT 3) 
        UNION 
        (SELECT * from tag_dir where tag_id=2 order by rand() LIMIT 2)
   )  tq
   JOIN questions ON questions.qid = tq.qid
   JOIN tags ON tags.id = tq.tag_id

You can add as many in the UNION as needed.

Upvotes: 1

GauthamGAjith
GauthamGAjith

Reputation: 377

This query returns 5 random results. You can change the number of results by modifying the LIMIT clause.

SELECT questions.qid, tags.tag 
    FROM tag_dir tag_dir 
JOIN questions ON questions.qid = tag_dir.qid 
JOIN tags ON tags.id = tag_dir.tag_id 
WHERE tags.id having 1 and 2
    ORDER BY RAND()
    LIMIT 5;

Reference

Upvotes: 0

Related Questions