CODEWITHSUNDEEP
mysqlsubquery
Mark Tower
Mark Tower

Reputation: 559

Query with subquery not returning all results

I am doing the next query:

SELECT id, name, keyt
FROM table
WHERE id = (SELECT t2.id FROM table t2 WHERE t2.keyt=21 ORDER BY RAND() LIMIT 1)

Supposing table is like this:

| id |  name      |  keyt   |
+ ------------------------- +
| 1  |  Hello     |  21     |
| 3  |  Katzet    |  1      |
| 1  |  Welcome   |  1      |
| 2  |  Two       |  21     |
| 2  |  Other     |  1      |

It should return one of this pairs:

So, the idea is:
Get one id, which has the keyt value set to 21
Then, get all the rows with this selected id (independently of all the other keyt values)

If I do as you suggested... I would get mixed id values, and all result rows must have the same id.

Upvotes: 1

Views: 558

Answers (3)

Raz Mahato
Raz Mahato

Reputation: 915

Your table has two 21 in the keyt column so your subquery in the where clause returns 2 values if id that is 1 and 2.So what you need to do is instead of using an equal to operator "=" use IN operator in the where clause.

SELECT id, name, keyt FROM table WHERE id IN (SELECT t2.id FROM table t2 WHERE t2.keyt=21 ORDER BY RAND())

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT id 
         FROM my_table 
        WHERE keyt = 21 
        ORDER 
           BY RAND() LIMIT 1
     ) y 
    ON y.id = x.id;

Upvotes: 1

Vivek Jain
Vivek Jain

Reputation: 3889

The subquery in this query

SELECT id, name, keyt FROM table WHERE id = (SELECT t2.id FROM table t2 WHERE t2.keyt=21 ORDER BY RAND() LIMIT 1)

would return only one record as it has LIMIT 1 added at the end.

Also, in your question, the table contains only 1 record for which value of keyt = 21, due to which you're getting only one record.

If you want more records, you should remove the LIMIT. In that case you may rephrase your query as:

SELECT id, name, keyt FROM table WHERE id IN (SELECT t2.id FROM table t2 WHERE t2.keyt=21 ORDER BY RAND())

Hope this is what you expected. As your actual goal is not very clear from the question.

Upvotes: 1

Related Questions