I need some help with case expression, subqueries and joins. I have the following two tables below

Table 1: Orders

Table 2: Progress

I'm trying to return all the results from Table 1 Orders and then if the order has a progress of "Delivered", I want to show that as well.
My intended results would exclude the following 4 columns and look like below:
Order
Order Date
Order By
Delivered (Case expression)

Results

This is what I tried code wise so far, but I know it's wrong.

    Select O.*, CASE WHEN(SELECT 1 FROM Progress WHERE Status = 'Delivered') THEN Y ELSE N END AS Delivered 
    FROM Orders O

CodePudding user response：

I think what you need here is an EXISTS as you want to check if the order has been delivered:

SELECT [Order], --Don't use Reserved Keywords for names; I strongly suggest changing this name
       [Order Date], --Try to avoid names that require delimit identification too
       [Order By], --ORDER BY is a clause is SQL too, so this isn't a great name choice either
       CASE WHEN EXISTS(SELECT 1
                        FROM dbo.Progress P
                        WHERE P.[Order] = O.[Order] 
                          AND P.Status = 'Delivered') THEN 'Y'
            ELSE 'N'
       END AS Delivered
FROM dbo.Orders O;

Alternatively, you could use aggregation and a JOIN, but this might be less ideal if you have more columns:

SELECT O.[Order],
       O.[Order Date],
       O.[Order By],
       MAX(CASE P.Status WHEN 'Delivered' THEN 'Y' ELSE 'N' END) AS Delivered
FROM dbo.Orders O
     JOIN dbo.Progress P ON O.[Order] = P.[Order] --I assume an order always has at least 1 status
GROUP BY O.[Order],
         O.[Order Date],
         O.[Order By];