SQL server query column yes or no 1

SQL server query column yes or no

SELECT  a.ID, a.FirstName, a.Lastname,
        CASE WHEN COUNT(b.CustID) > 0 THEN 'YES' ELSE 'NO' END AS HasOrders
FROM    Customer a
        LEFT JOIN Orders b
            ON a.ID = b.CustID        -- <<== the linking column on both tables
GROUP   BY a.ID, a.FirstName, a.Lastname

Here is what the above code is Doing:
1. The LEFT JOIN will return all records from the Customer table, and the matching records in the Orders table.
2. The COUNT(b.CustID) will count the number of matching records in the Orders table.
3. The CASE statement will return YES if the count is greater than 0, otherwise it will return NO.

Similar Posts