-3

I want to pick values from the same column using an and clause query, that looks something like this:

select * 
from degree_detail_result 
where course_id=1 and course_id=2 and course_id=3

What is wrong with the above query?

trincot
  • 317,000
  • 35
  • 244
  • 286
ghasif
  • 3
  • 1
  • 1
  • 3
  • 3
    Give example data and desired results. Those anded conditions are mutually exclusive for any row. – Martin Smith Nov 25 '16 at 21:43
  • 3
    Please *spell out* your condition. My best guess is that you need `OR` instead of `AND`. – PM 77-1 Nov 25 '16 at 21:44
  • 1
    Think about it: how can the same column contain the value 1 2 and 3 **at the same time**? –  Nov 26 '16 at 07:44

2 Answers2

3

Use in:

select * from degree_detail_result where course_id in (1,2,3)

Note that and will not work as it requires the conditions on either side of it to be true at the same time, which obviously is not possible.

If you want to get the results next to each other instead of in separate rows, you can use pivot:

select * from degree_detail_result
pivot (min(degree_id) for course_id in ([1], [2], [3])) as degree

If you want to get a parent ID (not mentioned in your question), for which there are records for course_id 1, 2 and 3, you can do this:

select   degree_id from degree_detail_result
where    course_id in (1,2,3)
group by degree_id
having   count(distinct course_id) = 3
trincot
  • 317,000
  • 35
  • 244
  • 286
  • ok supose i want to know what is degree id where the course is 1 and 2 and 3. tell me what i have to do ?? the above method tried it also fatch the degree id which have use the same comon id. – ghasif Nov 25 '16 at 21:47
  • 1
    Just select it? What you write can be interpreted in many ways. Please edit your question, provide sample data, and the output you expect to get from that data. – trincot Nov 25 '16 at 21:56
  • Probably a duplicate of http://stackoverflow.com/q/12563952/73226 based on that comment. – Martin Smith Nov 25 '16 at 22:14
2
select * 
from degree_detail_result 
where course_id=1 and course_id=2 and course_id=3

This query will not work, because this condition cannot be met at the same time.

You can try using the INTERSECT, but you need to specify the particular columns you are looking for instead of SELECT *

i.e. the following will return no rows

SELECT *
FROM   degree_detail_result
WHERE  course_id = 1
INTERSECT
SELECT *
FROM   degree_detail_result
WHERE  course_id = 2
INTERSECT
SELECT *
FROM   degree_detail_result
WHERE  course_id = 3;

and you would need

SELECT degree_id
FROM   degree_detail_result
WHERE  course_id = 1
INTERSECT
SELECT degree_id
FROM   degree_detail_result
WHERE  course_id = 2
INTERSECT
SELECT degree_id
FROM   degree_detail_result
WHERE  course_id = 3;
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Vikram
  • 197
  • 2
  • 6