CODEWITHSUNDEEP
sqloracle-database
Mohit
Mohit

Reputation: 41

Multiple where clause for 2 column

My table structure is like this:

sch_id  daynum  sched_hrs
123        1        7
123        2        6.5
123        3        5
456        1        8
456        2        4
456        3        3

I want to create a query which will get me the all the schedule where daynum = 1 and sched_hrs = 7 and daynum = 2 and sched_hrs = 6.5.

I am trying this approach, but not getting any luck with it

select distinct sch_id from table
  where (daynum,sched_hrs) in (('1', '7')) and (daynum,sched_hrs) in (('2','6.5')) .

Upvotes: 2

Views: 65

Answers (7)

Herman
Herman

Reputation: 1

If I understand your question correctly, a simple UNION would grab all the records you are looking for:

select sch_id,daynum,sched_hrs from table where daynum = '1' and sched_hrs = '7'
union
select sch_id,daynum,sched_hrs from table where daynum = '2' and sched_hrs = '6.5';

Upvotes: 0

Aramillo
Aramillo

Reputation: 3226

Perhaps you want this:

select * from t t1 where daynum=1 and sched_hrs = 7 and 
exists (select 1 from t where sch_id = t1.sch_id and daynum=2 and sched_hrs = 6.5)
union all
select * from t t1 where daynum=2 and sched_hrs = 6.5 and 
exists (select 1 from t where sch_id = t1.sch_id and daynum=1 and sched_hrs = 7)

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52070

Given your various comment, it seems that you are in fact searching for a "sch_id" having both a sched_hrs of 7 the first day, and a sched_hrs of 6.5 the second day.

If it's so, you might solve that using a self-join:

SELECT "sch_id" FROM T t1 JOIN T t2 USING("sch_id")
WHERE t1."daynum" = 1 AND t1."sched_hrs" = 7
  AND t2."daynum" = 2 AND t2."sched_hrs" = 6.5

See http://sqlfiddle.com/#!4/59631/1/0

Upvotes: 1

David Faber
David Faber

Reputation: 12495

If I understand you correctly, what you want is an OR:

 WHERE ( daynum = 1 AND sched_hrs = 7 ) OR ( daynum = 2 AND sched_hrs = 6.5 )

I've used parentheses in the above even though they're not absolutely necessary in this case (AND takes precedence over OR).

You might also use IN (should give the same result):

WHERE (daynum, sched_hrs) IN ( (1, 7), (2, 6.5) )

Upvotes: 1

Brian Leeming
Brian Leeming

Reputation: 11750

    select * from test where daynum=1 and sched_hrs = 7 and sch_id in (select sch_id from test where daynum=1 )
union
select * from test where daynum=2 and sched_hrs = 6.5 and sch_id in (select sch_id from test where daynum=2 )

Upvotes: 0

Jens
Jens

Reputation: 69505

This should give you the correct result:

select sch_id from table t1 join t2 on t1.sch_id  =t2.sch_id  
  where t1.daynum =1 and t1.sched_hrs = 7 and t2.daynum =2 and t2.sched_hrs = 6.5

Upvotes: 1

Onur Cete
Onur Cete

Reputation: 273

You can try;

  select sch_id
from table
   where daynum in (1, 2)
 and sched_hrs in (7, 6.5)

Upvotes: 0

Related Questions