SQL Server – Help to Build SQL Query for a Complex Scenario

sql server

I need help to build an SQL query for a slightly complex scenario. I have an interface with the following dropdowns.

Cuisine: Any, Chinese, Indian, Italian, French

Delivery type: Any, Delivery, Pickup, Dine-In

Meal type: Any, Breakfast, Lunch, Dinner

Cuisine Table

CuisineId  | Type
-------------------
     1     | Chinese
     2     | Indian
     3     | Italian
     4     | French

Delivery type Table

DeliveryTypeId | Type
--------------------------
         1     | Delivery
         2     | Pickup
         3     | Dine In

Meal type Table

    MealTypeId | Type
--------------------------
         1     | Breakfast
         2     | Lunch
         3     | Dinner

Food Table

    Food            CuisineId   MealTypeId      DeliveryTypeId
---------           -------     ---------       -------------
Cakes               NULL         NULL            NULL
Pizza               3 (Italian)  NULL            NULL
Noodles             1 (Chinese)  2 (Lunch)       NULL
Butter Chicken      2 (Indian)   3 (Dinner)      1 (Delivery)
Ice cream           NULL         2 (Lunch)       2 (Pickup)
Soup                NULL        NULL             2 (Pickup)
Drinks              NULL        NULL             1 (Delivery)

Please note: In the above table, NULL represents the selection of Any from the dropdown.

The logic here is as follows:

Cuisine: if the option is selected as Any or if the selected option doesn't have a matching value in the corresponding column in Food table, then show items that have NULL in Cuisine column.

Delivery Type: default should be Delivery / NULL unless Pickup or Dine In is selected

Meal type: Show only NULL (Any) items unless any specific option is selected.

For example, please find the sample output based on the selections.

Cuisine     Meal type       Delivery Type       Output
-------     ----------      -------------       ------
Any         Any             Any                 Cakes,Drinks
Any         Any             Delivery            Cakes,Drinks
Any         Lunch           Any                 NO RECORDS
Any         Any             Pickup              Soup
French      Any             Any                 Cakes,Drinks
French      Any             Delivery            Cakes,Drinks
French      Lunch           Any                 NO RECORDS
French      Any             Pickup              Soup
Chinese     Any             Any                 NO RECORDS
Chinese     Lunch           Any                 Noodles
Indian      Any             Any                 NO RECORDS
Indian      Dinner          Any                 Butter Chicken

How can I build an SQL query for the above scenario. Any help is highly appreicated. Thanks in advance.

The query will be used in a stored proc and the parameters passed will be @cusineType (like Chinese or French), @deliveryType (like Delivery or Pickup) and @mealType (like Breakfast or Lunch) or NULL for any of these.

Script for the above tables with data here: script.sql

Best Answer

I would prefer not to have NULLs in that food table, sticking with your schema this seems to do it

declare @cuisine nvarchar(10) = 'Chinese'
declare @mealtype nvarchar(10) = 'Lunch'
declare @deliverytype nvarchar(10) = 'Any'

select @cuisine = (select cuisineid from #cuisine where v_type = @cuisine)
select @deliverytype = (select deliverytypeid from #delivery where v_type = @deliverytype)
select @mealtype = (select mealtypeid from #meal where v_type = @mealtype)

if not exists (select 1 from #food where cuisineid = @cuisine)
    select @cuisine = null

;with t1 as (
    select f.food
      from #food f
      left join #cuisine c
        on f.cuisineid = c.cuisineid
      where 1=1
        and f.cuisineid is null
        and @cuisine is null
    union

    select f.food
      from #food f
      join #cuisine c
        on f.cuisineid = c.cuisineid
      where 1=1
        and c.cuisineid = @cuisine
), t3 as (
    select f.food
      from #food f
      left join #meal c
        on f.meatltypeid = c.mealtypeid
      where 1=1
        and f.meatltypeid is null
        and @mealtype is null
    union

    select f.food
      from #food f
      join #meal c
        on f.meatltypeid = c.mealtypeid
      where 1=1
        and f.meatltypeid = @mealtype
), t5 as (
    select f.food
      from #food f
      left join #delivery c
        on f.deliverytypeid = c.deliverytypeid
      where 1=1
        and f.deliverytypeid is null
        and @deliverytype is null

    union

    select f.food
      from #food f
      where 1=1
        and f.deliverytypeid = 1
        and @deliverytype is null       

    union

    select f.food
      from #food f
      join #delivery c
        on f.deliverytypeid = c.deliverytypeid
      where 1=1
        and f.deliverytypeid = @deliverytype

    union

    select f.food
      from #food f
      where 1=1
        and (f.deliverytypeid is null or f.deliverytypeid = 1)
        and (@deliverytype = 1)

)

select t.food
  from (
select t1.food
  from t1
  join t3
    on t1.food = t3.food
) t
join t5
  on t.food = t5.food

db fiddle which contains test schema and data.

Related Question