CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
Muhammad Asif Raza
Muhammad Asif Raza

Reputation: 677

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   |

Upvotes: 0

Views: 1432

Answers (4)

cdonat
cdonat

Reputation: 2822

Produce the additional information in a sub select, that you simply join to the original table:

SELECT
    ac.code_id,
    ac.atb_name_id,
    CASE WHEN f.count = 1 THEN 'yes'
         ELSE 'no' END AS flag
FROM
    attribute_combinations ac INNER JOIN
    (SELECT
        products_id,
        COUNT(DISTINCT atb_name_id) AS count
    FROM
        attribute_combinations
    GROUP BY
        products_id) f ON ac.products_id = f.products_id
WHERE
   ac.products_id = 109

Beware: I have not tested this code. It is just here to give you an idea and might contain bugs.

Since the MySQL query optimizer is not really always the best, you might get better performance with restricting the result of the subselect to only the products, you are interested in:

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

Upvotes: 0

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

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

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use CASE expression.

Query

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

Upvotes: 0

Ssekiziyivu Godfrey
Ssekiziyivu Godfrey

Reputation: 9

Try to use select distinct ... I hope it will work well.

Upvotes: 0

Related Questions