Need help to build SQL query for a complex scenario 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
Exactly, How you will pass  parameter value ? 

In my example, I am passing parameter value in variable.

Mention it clearly.I understand that above output is only for explanation.

create table #Cuisine(CuisineId int,CuisineType varchar(50))
insert into #Cuisine values
( 1 ,'Chinese'),( 2 ,'Indian'),( 3 ,'Italian'),( 4 ,'French')

create table #DeliveryType (DeliveryTypeId int, DeliveryType varchar(50))
insert into #DeliveryType values
 (  1,'Delivery') ,(  2,'Pickup') ,(  3,'Dine In')

  create table #MealType (MealTypeId int, MealType varchar(50))
 insert into #MealType values
(1,'Breakfast')
,(2,'Lunch')
,(3,'Dinner')


 create table #Food(Food  varchar(50),CuisineId int,MealTypeId int,DeliveryTypeId int)
insert into #Food values
('Cakes',               NULL ,        NULL,          NULL)
,('Pizza',               3 ,  NULL,            NULL)
,('Noodles',             1 ,  2 ,       NULL)
,('Butter Chicken',      2  ,  3,       1 )
,('Ice cream',           NULL,         2 ,      2 )
,('Soup',                NULL,        NULL,             2 )
,('Drinks',              NULL,        NULL,             1 )


Parameter value,

Declare @Cuisine int,@Mealtype int,@DeliveryType int=2


Since you have condition like if delivery is Any then consider both null and Delivery=1 and vice versa.

So Create one local temp table to define value.It is easier and cost effective to join than write complicated and expensive where condition.

Create table #DeliveryParameter (DeliveryTypeId int)

if(@DeliveryType is null or @DeliveryType=1)
insert into #DeliveryParameter values(-1),(1)
else
insert into #DeliveryParameter values(@DeliveryType)


select * from #Food F
inner join #DeliveryParameter DP on f.DeliveryTypeId=dp.DeliveryTypeId
where ((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine)) 
and ((@Mealtype is null and MealTypeId is null) or(MealTypeId=@Mealtype)) 

drop table #Cuisine,#DeliveryType,#MealType,#Food,#DeliveryParameter


Notice : ((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine)) 

This type of condition is very expensive if you records are in millions .

So if you treat Null or Any as -1 or 0 for example, then condition will be simplified to

CuisineId=@Cuisine


Like in #Food table store -1 instead of Null and in variABLE pass -1 instead of null.

If you need output in some other format then mention it.