Not getting distinct results using case query 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#&gt;0 then 'Y' else 'n' end as DeliveryBeerYN ,
    case when storenumbers.storenumber is not null and concept ='Beer' and dinein#&gt;0 then 'Y' else 'n' end as DineInBeerYN,
    case when storenumbers.storenumber is not null and menusize='WithMeal' and dinein#&gt;0 then 'Y' 
else 'n' end as SaladBar 
from storenumbers 
    left join prod5219 on storenumbers.storenumber = [Prod5219].store
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.