Fetch data from table based on multiple columns from same table

Question

I have a table of course :

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
       substream=@substream)
   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?

Use ColName LIKE '%YourValue% instead of ColName = YourValue — Siyavash Hamdi
– Siyavash Hamdi, Commented May 4, 2016 at 9:23

Abdul Rasheed · Accepted Answer · 2016-05-04 09:23:44Z

2

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)

answered May 4, 2016 at 9:23

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

shashank Over a year ago

abdul is that possible without using the value null i can get the result.

Abdul Rasheed Over a year ago

use default NULL while declaring this parameters, then no need to pass or assign this.

shashank Over a year ago

i tried but not getting results. In sql query is working perfectly but when i run this query into website and passing the parameters then i'm not getting result because the parameters are passing ' ' blank value not null.

Abdul Rasheed Over a year ago

what is the datatype of parameters ? try with Null-able Int datatype. Otherwise try to pass DBNULL instead of ' ' blank value.

shashank Over a year ago

i passed DBNULL also and other thing also but still it is passing blank value i'm using dropdownlist in asp.net c# from there i'm selecting value and i passed null value on first index of DDL.

|

jarlh · Accepted Answer · 2016-05-04 09:23:45Z

2

For each category, check if the parameter is null (not given) or the same as specified:

select *
from edu_college_desc
where (@country is null or country = @country)
  and (@university is null or university = @university)
  and etc...

And, of course, you can also use MS SQL Server's ISNULL, just as in Abdul Rasheed answer. Convenient but less portable.

answered May 4, 2016 at 9:23

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

Mahesh Bhosale · Accepted Answer · 2016-05-04 09:48:54Z

1

Your query should be like :

SELECT * FROM edu_college_desc WHERE (@country IS NULL OR country = @country) AND (@university IS NULL OR university = @university) AND (@level IS NULL OR level = @level ) AND (@interest IS NULL OR interest = @interest) AND (@substream IS NULL OR substream= @substream)

answered May 4, 2016 at 9:48

Mahesh Bhosale

11211 bronze badges

Comments

Pankaj Kumar · Accepted Answer · 2016-05-04 10:49:38Z

0

This code will definitely help you.

SELECT * FROM edu_college_desc WHERE (country = @country or isnull(@country,0)= 0) AND (university = @university or isnull(@university,0)= 0) AND (leveln = @leveln or isnull(@leveln,0)= 0) AND (interest = @interest or isnull(@interest,0)= 0) AND (substream=@substream or isnull(@substream,0)= 0)

answered May 4, 2016 at 10:49

Pankaj Kumar

474 bronze badges

Collectives™ on Stack Overflow

Fetch data from table based on multiple columns from same table

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related