2

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 
  • Without a data sample to look at, it's hard to say, but it seems like there are multiple records in your [storenumbers] table (or [prod5219] table) that match, and if so it makes sense that you would get multiple rows. What you probably want is a query that checks the existence of a field value instead of a join. – BlueGI May 2 '19 at 20:28
  • @Jeff - welcome to Database Administrators - please take a quick look at the tour and read about how to create a minimally complete and verifiable example when posting questions with code. – Max Vernon May 2 '19 at 20:34
2

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.

1

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.