Not getting distinct results using case query

Question

This is the query I am running. I am getting the correct results except they come in on a different line for each corpstorenum, so i am getting :

Corpstorenum,BuffetYN, deliverybeeryn, dineinbeeryn,saladbar
store1,n,n,n,y
store1,n,n,y,n
store1,n,y,n,n
store1,n,n,n,n

instead of:

store1 n,y,y,y

This is my code:

select distinct corpstorenum,
case when storenumbers.storenumber is not null and concept ='buffetAY' then 'Y' else 'n' end as BuffetYN ,
    case when storenumbers.storenumber is not null and concept ='Beer' and delivery#>0 then 'Y' else 'n' end as DeliveryBeerYN ,
    case when storenumbers.storenumber is not null and concept ='Beer' and dinein#>0 then 'Y' else 'n' end as DineInBeerYN,
    case when storenumbers.storenumber is not null and menusize='WithMeal' and dinein#>0 then 'Y' 
else 'n' end as SaladBar 
from storenumbers 
    left join prod5219 on storenumbers.storenumber = [Prod5219].store 

Answer 1

You have multiple rows in prod5219 for store store1, and each row has a unique set of values for concept and menusize.

Run this to check that out:

select storenumbers.*
    , prod5219.concept
    , prod5219.menusize
from storenumbers 
    left join prod5219 on storenumbers.storenumber = [Prod5219].store 

Simply adding distinct to the select clause doesn't magically remove "extra" rows from the output. distinct simply returns rows that are unique, across all columns returned by the query.

Answer 2

Not sure what you want to achieve, but I assume that you would like 'Y' if that exists. Since 'Y' > 'n' you can use max as an aggregate function. I.e.

select corpstorenum
      , max(case when storenumbers.storenumber is not null and concept ='buffetAY' 
                 then 'Y' else 'n' end) as BuffetYN 
      , max(case when storenumbers.storenumber is not null and concept ='Beer' and delivery#>0 
                 then 'Y' else 'n' end) as DeliveryBeerYN 
      , max(case when storenumbers.storenumber is not null and concept ='Beer' and dinein#>0 
                 then 'Y' else 'n' end) as DineInBeerYN
      , max(case when storenumbers.storenumber is not null and menusize='WithMeal' and dinein#>0 
                 then 'Y' else 'n' end) as SaladBar 
from storenumbers 
left join prod5219 
    on storenumbers.storenumber = [Prod5219].store 
group by corpstorenum

Answer 3

As per my comment, you might want to re-write your query to just check for the existence of records. Something like the following (just an example, change as needed or if the tables are reversed).

SELECT p.corpstorenum,
       CASE WHEN EXISTS(SELECT * FROM [storenumbers] WHERE storenumber = p.store AND concept = 'buffetAY') THEN 'Y' ELSE 'n' END AS BuffetYN,
       CASE WHEN EXISTS(SELECT * FROM [storenumbers] WHERE storenumber = p.store AND concept = 'beer' and delivery#>0) THEN 'Y' ELSE 'n' END AS DeliveryBeerYN,
       CASE WHEN EXISTS(SELECT * FROM [storenumbers] WHERE storenumber = p.store AND concept = 'beer' AND dinein#>0) THEN 'Y' ELSE 'n' END AS DineInBeerYN,
       CASE WHEN EXISTS(SELECT * FROM [storenumbers] WHERE storenumber = p.store AND menusize='WithMeal' and dinein#>0) THEN 'Y' ELSE 'n' END AS SaladBar
FROM prod5219 p