Question 363732 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql query filter rows based on a constant column value and a varying boolean column

I have a MySQL table

Cat User Active
12 45 0
12 54 0
13 76 0
12 33 1
15 66 0
12 12 0
12 22 0

I run a query to select based on category id(cat).

fore ex. If I am querying for cat 12 then the rows with active 1 should be the result even if there are rows with active=0.

Cat User Active
12 33 1

If active= 1 is not present and if there are active = 0 then the rows with active=0 should be the result.

Cat User Active
12 45 0
12 54 0
12 12 0
12 22 0

Hope it should be possible to do all these in one single query with IF.
But, I could not figure out. Need help.

Thanks

Tags:

2

Answers


  1. 0

    What you could do is on your WHERE clause check the COUNT() of Active rows and depending on the number perform the appropriate equality. For example:

     SELECT *
    FROM your_table_name
    WHERE Cat = 12 AND Active = (CASE WHEN (SELECT COUNT(*) FROM your_table_name WHERE Cat = 2 AND Active = 1)>0 THEN 1 ELSE 0 END)

    https://dbfiddle.uk/4K8DHHmw

    Login or Signup to reply.
  2. 0 
    SELECT t1.*
FROM test t1
JOIN (SELECT cat, MAX(active) AS active
      FROM test
      GROUP BY cat) AS subquery USING (cat, active)

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search