SQL:Query to check if a column meets certain criteria, if it does perform one action if it doesn't perform another

I have found it quite hard to word what I want to do in the title so I will try my best to explain now!

I have two tables which I am using:

Master_Tab and Parts_Tab

Parts_Tab has the following information:

Order_Number | Completed| Part_Number|

   |    1       |     Y    |     64     |
   |    2       |     N    |     32     |
   |    3       |   开发者_JAVA百科  Y    |     42     |
   |    1       |     N    |     32     |
   |    1       |     N    |     5      |

Master_Tab has the following information:

Order_Number|
 1           |  
 2           |
 3           |
 4           |
 5           |

I want to generate a query which will return ALL of the Order_Numbers listed in the Master_Tab on the following conditions...

For each Order_Number I want to check the Parts_Tab table to see if there are any parts which aren't complete (Completed = 'N'). For each Order_Number I then want to count the number of uncompleted parts an order has against it. If an Order_Number does not have uncompleted parts or it is not in the Parts_Table then I want the count value to be 0.

So the table that would be generated would look like this:

Order_Number | Count_of_Non_Complete_Parts|
1            |            2               |
2            |            1               |
3            |            0               |
4            |            0               |
5            |            0               |

I was hoping that using a different kind of join on the tables would do this but I am clearly missing the trick!

Any help is much appreciated!

Thanks.

I have used COALESCE to convert NULL to zero where necessary. Depending on your database platform, you may need to use another method, e.g. ISNULL or CASE.

select mt.Order_Number, 
    coalesce(ptc.Count, 0) as Count_of_Non_Complete_Parts
from Master_Tab mt
left outer join (
    select Order_Number, count(*) as Count
    from Parts_Tab 
    where Completed = 'N'
    group by Order_Number
) ptc on mt.Order_Number = ptc.Order_Number
order by mt.Order_Number

You are looking for a LEFT JOIN.

SELECT mt.order_number, count(part_number) AS count_noncomplete_parts
FROM master_tab mt LEFT JOIN parts_tab pt
ON mt.order_number=pt.order_number AND pt.completed='N'
GROUP BY mt.order_number;

It is also possible to put pt.completed='N' into a WHERE clause, but you have to be careful of NULLs. Instead of the AND you can have

WHERE pt.completed='N' OR pr.completed IS NULL

SELECT mt.Order_Number SUM(tbl.Incomplete) Count_of_Non_Complete_Parts
FROM Master_Tab mt
    LEFT JOIN (
       SELECT Order_Number, CASE WHEN Completed = 'N' THEN 1 ELSE 0 END Incomplete
       FROM Parts_Tab
     ) tbl on mt.Order_Number = tbl.Order_Number
GROUP BY mt.Order_Number

Add a WHERE clause to the outer query if you need to filter for specific order numbers.

I think it's easiest to get a subquery in there. I think this should be self-explanitory, if not feel free to ask any questions.

CREATE TABLE #Parts
(
    Order_Number int,
    Completed char(1),
    Part_Number int
)

CREATE TABLE #Master
(
    Order_Number int
)

INSERT INTO #Parts
SELECT 1, 'Y', 64 UNION ALL
SELECT 2, 'N', 32 UNION ALL
SELECT 3, 'Y', 42 UNION ALL
SELECT 1, 'N', 32 UNION ALL
SELECT 1, 'N', 5 

INSERT INTO #Master
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6

SELECT M.Order_Number, ISNULL(Totals.NonCompletedCount, 0) FROM #Master M
    LEFT JOIN (SELECT P.Order_Number, COUNT(*) AS NonCompletedCount FROM #Parts P
                WHERE P.Completed = 'N'
                GROUP BY P.Order_Number) Totals ON Totals.Order_Number = M.Order_Number