shashank shashank - 5 months ago 15x
SQL Question

Fetch data from table based on multiple columns from same table

I have a table of course :

enter image description here

I want to fetch data from searching based on columns Country, university, level, interest and substream.

Query which I've tried but not getting better result.

select *
from edu_college_desc
where (country = @country and
university = @university and
leveln = @level and
interest = @interest and
or (country = @country or
university = @university or
leveln = @level or
interest = @interest or
substream = @substream)

What I want to do is: if select only country then the data should come based on only country or if I select only stream then data fetched based from stream only if I select both or more then data fetch should be based on those columns.

How can I get perfect results?


Try this method,

select * from edu_college_desc 
where   country     =   ISNULL(@country ,country)
    and university  =   ISNULL(@university ,university)
    and leveln      =   ISNULL(@level ,leveln)
    and interest    =   ISNULL(@interest ,interest
    and substream   =   ISNULL(@substream,substream)

In this you can pass the value NULL to any of the parameters if it is not selected.(ie If you have set value only for @university and others are NULL, then result will be university = @university)