AdventureWorks Database: Evaluate whether the values in two columns are the same

SQL Query - AdventureWorks: Exercise-129 with Solution

129. From the following table write a query in SQL to evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same for products with ProductID less than 10. If the values in MakeFlag and FinishedGoodsFlag are the same, return NULL; otherwise, return the value of MakeFlag. Return the columns ProductID, MakeFlag, FinishedGoodsFlag, and the evaluated result.

productid|name                            |productnumber|makeflag|finishedgoodsflag|color       |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate          |sellenddate            |discontinueddate|rowguid                             |modifieddate           |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
        1|Adjustable Race                 |AR-5381      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
        2|Bearing Ball                    |BA-8327      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
        3|BB Ball Bearing                 |BE-2349      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
        4|Headset Ball Bearings           |BE-2908      |false   |false            |            |             800|         600|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
      316|Blade                           |BL-2036      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
      317|LL Crankarm                     |CA-5965      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |L    |     |                    |              |2008-04-30 00:00:00.000|                       |                |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
      318|ML Crankarm                     |CA-6738      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |M    |     |                    |              |2008-04-30 00:00:00.000|                       |                |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the Product table and applying a CASE statement to compare MakeFlag and FinishedGoodsFlag
SELECT 
    -- Selecting the ProductID column
    ProductID, 
    -- Selecting the MakeFlag column
    MakeFlag, 
    -- Selecting the FinishedGoodsFlag column
    FinishedGoodsFlag, 
    -- Applying a CASE statement to handle conditions based on MakeFlag and FinishedGoodsFlag
    CASE  
        -- If MakeFlag is equal to FinishedGoodsFlag, return NULL
        WHEN MakeFlag = FinishedGoodsFlag THEN NULL  
        -- If MakeFlag is not equal to FinishedGoodsFlag, return MakeFlag
        ELSE MakeFlag  
    END  
FROM 
    Production.Product  
-- Filtering records where ProductID is less than 10
WHERE 
    ProductID < 10;

Explanation:

• This SQL code selects specific columns from the Product table and applies a CASE statement to handle conditions based on MakeFlag and FinishedGoodsFlag.
• The CASE statement compares the values of MakeFlag and FinishedGoodsFlag for each row:
• If MakeFlag is equal to FinishedGoodsFlag, NULL is returned.
• If MakeFlag is not equal to FinishedGoodsFlag, MakeFlag itself is returned.
• The result set includes columns for ProductID, MakeFlag, and FinishedGoodsFlag, as well as the result of the CASE statement.
• Records are filtered to include only those where ProductID is less than 10.
• This SQL query can be used to identify products where MakeFlag and FinishedGoodsFlag are different, indicating potential inconsistencies in data or manufacturing status.

Sample Output:

productid|makeflag|finishedgoodsflag|makeflag|
---------+--------+-----------------+--------+
        1|false   |false            |        |
        2|false   |false            |        |
        3|true    |false            |true    |
        4|false   |false            |        |

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Change the display of product line categories with comment.
Next: Select data from first column that has a nonnull value.