CODEWITHSUNDEEP
mysqlsqlvb.net
earl chester siaotong
earl chester siaotong

Reputation: 103

Combine 4 SELECT queries on mysql

Im trying to combine these 3 mysql statements. I've been using the UNION and JOIN Keyword however it doesnt come up with the output I desired. Any suggestions? This is my 3 statements.

SELECT * FROM entrancequestion
WHERE Subject='Abstract Reasoning'
ORDER BY RAND()
LIMIT 10

SELECT * FROM entrancequestion
WHERE Subject='English'
ORDER BY RAND()
LIMIT 30

SELECT * FROM entrancequestion
WHERE Subject='Mathematics'
ORDER BY RAND()
LIMIT 30

SELECT * FROM entrancequestion
WHERE Subject='Science'
ORDER BY RAND()
LIMIT 30

I tried combining the first 2 statements like so:

SELECT * FROM entrancequestion
WHERE Subject='Abstract Reasoning'
LIMIT 10
UNION
SELECT * FROM entrancequestion
WHERE Subject='English'
ORDER BY RAND()
LIMIT 30;

however it only reads the second LIMIT query wherein t outputs 30 rows only.

I would like to create a query where in a total of 100 rows is displayed and randomized according to index. Your help would be much appreciated.

Upvotes: 4

Views: 102

Answers (4)

ntaso
ntaso

Reputation: 614

If you don't care so much about the first one having LIMIT 10:

SELECT * FROM entrancequestion
WHERE
    Subject='Abstract Reasoning' OR
    Subject='English' OR
    Subject='Mathematics' OR
    Subject='Science'
ORDER BY RAND()
LIMIT 30

Upvotes: 0

Ravi Dhoriya ツ
Ravi Dhoriya ツ

Reputation: 4414

Quoting the docs,

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT * FROM entrancequestion
WHERE Subject='Abstract Reasoning'
ORDER BY RAND()
LIMIT 10)

UNION

(SELECT * FROM entrancequestion
WHERE Subject='English'
ORDER BY RAND()
LIMIT 30)

UNION

(SELECT * FROM entrancequestion
WHERE Subject='Mathematics'
ORDER BY RAND()
LIMIT 30)

UNION

(SELECT * FROM entrancequestion
WHERE Subject='Science'
ORDER BY RAND()
LIMIT 30 )

Upvotes: 3

iBeAtWork
iBeAtWork

Reputation: 1

Try This:

 SELECT TOP 10 * FROM entrancequestion
 WHERE Subject='Abstract Reasoning'
 UNION
 SELECT TOP 30 * FROM entrancequestion
 WHERE Subject='English'
 ORDER BY RAND()

Upvotes: 0

user3332631
user3332631

Reputation: 394

You need to use parentheses in the query when using UNION and LIMIT, like:

(SELECT * FROM entrancequestion WHERE Subject='Abstract Reasoning' LIMIT 10)
UNION
(SELECT * FROM entrancequestion WHERE Subject='English' ORDER BY RAND() LIMIT 30);

Upvotes: 3

Related Questions