1

I want to use a query just like this; but I know it's not true usage.

In total I need 15 category ids (4,5,6,9,10 and random 10 ids other than the specified)

Tried this

SELECT * 
FROM categories 
WHERE id IN (CONCAT('4,5,6,9,10', SELECT id from categories WHERE id NOT IN(4,5,6,9,10) ORDER BY RAND())) 
LIMIT 0,10

Could you help me to edit this one?

Thanks a lot.

Vamsi Krishna
  • 3,742
  • 4
  • 20
  • 45
vural
  • 381
  • 2
  • 11

2 Answers2

3

You cannot mix constants and subqueries in an in clause. The following fixes the syntax errors, but doesn't actually solve the problem:

SELECT * 
FROM categories 
WHERE id IN (4,5,6,9,10) or
      id IN (SELECT id from categories WHERE id NOT IN (4,5,6,9,10) ORDER BY RAND()
            ) 
LIMIT 0,15;

You can do what you want this way:

select *
from categories
order by (id in (4, 5, 6, 9, 10)) desc,
      rand()
limit 15;

This will return 10 rows, 5 of which are your desired ids and the rest of which are randomly chosen. (This assumes that id is unique in the categories table.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

try that

   SELECT * 
   FROM categories 
   WHERE id IN (4,5,6,9,10)
   or id in(
   SELECT id from categories WHERE id NOT IN(4,5,6,9,10) ORDER BY RAND()
           )
   LIMIT 0,10
echo_Me
  • 37,078
  • 5
  • 58
  • 78