CODEWITHSUNDEEP
sqlsql-serverjoinduplicatescase
cheunghm4532
cheunghm4532

Reputation: 57

SQL: How to remove duplicate rows created by CASE WHEN statement

I have two tables: (A) customers of the gym and (B) customers of the restaurant. I want to create an indicator in table (A) to indicate the customers who have been to both the gym and the restaurant on the same day. In accomplishing this, I used the following SQL script, but it created duplicate rows:

SELECT *,
CASE WHEN a.GymDate = b.RestaurantDate THEN 'Meal + Gym on the same day'
ELSE 'Gym Only' END AS 'Meal+Gym'
FROM Table_A a
LEFT JOIN Table_B b
ON a.customerid = b.customerid;

May I know how to keep only Table_A, but with the addition of the 'Meal+Gym' Indicator? Thanks!

Upvotes: 0

Views: 7007

Answers (3)

Peter B
Peter B

Reputation: 24187

You can use CASE WHEN EXISTS instead of the LEFT JOIN:

SELECT *,
CASE WHEN EXISTS (
   SELECT 1 FROM Table_B b
   WHERE a.customerid = b.customerid
   AND a.GymDate = b.RestaurantDate)
   THEN 'Meal + Gym on the same day'
   ELSE 'Gym Only'
   END AS 'Meal+Gym'
FROM Table_A a

This assumes that you don't need any data from Table_B in the results.

Upvotes: 1

GarethD
GarethD

Reputation: 69769

A case expression does not generate rows, it is your join that is generating the duplicate rows. You could add the date predicate to the join condition, and merely check for the existence of a record, e.g.

SELECT *,
        CASE WHEN b.customerid IS NOT NULL THEN 'Meal + Gym on the same day'
            ELSE 'Gym Only' 
        END AS [Meal+Gym]
FROM Table_A a
      LEFT JOIN Table_B b
          ON a.customerid = b.customerid
          AND a.GymDate = b.RestaurantDate;

If table_B is not unique per customer/Date then you may need to do something like this to prevent duplicates:

SELECT *,
       CASE WHEN r.RestaurantVisit IS NOT NULL THEN 'Meal + Gym on the same day'
            ELSE 'Gym Only' 
       END AS [Meal+Gym]
FROM Table_A a
    OUTER APPLY 
    (    SELECT TOP 1 1
         FROM   Table_B b
         WHERE  a.customerid = b.customerid
         AND    a.GymDate = b.RestaurantDate
    ) AS r (RestaurantVisit);

N.B. While using single quotes works for column aliases, it is not a good habit at all, because it makes your column aliases indistinguishable from string literals other than from context. Even if this is clear to you, it probably isn't to other people, and since there's about a 10:1 ratio of reading:writing code, writing code that is easy to read is important. As such I've used square brackets for your column name instead

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would start with a table of customers, so you get an indicator for customers who have been to neither the gym nor a restaurant.

Then:

select c.*,
       (case when exists (select 1
                          from table_a a join
                               table_b b
                               on a.customerid = b.customerid and
                                  a.GymDate = b.RestaurantDate
                          where a.customerid = c.customerid
                         )
             then 1 else 0
         end) as same_day_gym_restaurant_flag
from customers c;

Upvotes: 1

Related Questions