Muhammad Asif Raza Muhammad Asif Raza - 6 months ago 8
SQL Question

Mysql check if values are same or not

I am fetching records from a table and it returns me a set of mixed data

here is a simple query

SELECT code_id, atb_name_id FROM `attribute_combinations` WHERE products_id =109


It returns me data like this

| code_id |atb_name_id|
-----------------------
| 1 | 31 |
| 2 | 31 |
| 3 | 31 |
| 4 | 31 |
| 5 | 31 |
| 6 | 34 |
| 7 | 34 |
| 8 | 34 |
| 9 | 34 |


I want to make another alias "flag" that will have all values filled with "yes" if "atb_name_id" column has all same values otherwise filled with "no".

In the above example as "atb_name_id" has both set of 31 and 34 so the output will be

| code_id |atb_name_id| flag |
------------------------------
| 1 | 31 | no |
| 2 | 31 | no |
| 3 | 31 | no |
| 4 | 31 | no |
| 5 | 31 | no |
| 6 | 34 | no |
| 7 | 34 | no |
| 8 | 34 | no |
| 9 | 34 | no |

Answer

You can do the following:

SELECT code_id, atb_name_id,
       (SELECT CASE WHEN COUNT(DISTINCT atb_name_id) > 1 THEN 'no' ELSE 'yes' END
        FROM `attribute_combinations` ac2
        WHERE products_id = ac.products_id) AS flag
FROM `attribute_combinations` ac
WHERE products_id =109
Comments