CODEWITHSUNDEEP
sqloracle-database
Nagaraj B
Nagaraj B

Reputation: 79

SQL IN clause with multiple parameters

I have a table like below

CREATE TABLE student_marks (Name, Subject, Marks ) AS
  SELECT 'Mike', 'Physics',   20 FROM DUAL UNION ALL
  SELECT 'Mike', 'Bio',       20 FROM DUAL UNION ALL
  SELECT 'Mike', 'Chemistry', 30 FROM DUAL UNION ALL
  SELECT 'Ryan', 'Physics',   24 FROM DUAL UNION ALL
  SELECT 'Ryan', 'Bio',       24 FROM DUAL UNION ALL
  SELECT 'Ryan', 'Chemistry', 24 FROM DUAL;

I want to get the name and marks of Mike for Physics and for all the subjects for Ryan. Query I want to use is something like this

select *
from   student_marks
where  (Name,Subject) in (('Mike','Physics'),('Ryan',*));

What should I replace * in the query so that it matches all the subjects for Ryan?

Upvotes: 0

Views: 1692

Answers (1)

MT0
MT0

Reputation: 167842

Replace * with Subject:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE student_marks (Name, Subject, Marks ) AS
  SELECT 'Mike', 'Physics',   20 FROM DUAL UNION ALL
  SELECT 'Mike', 'Bio',       20 FROM DUAL UNION ALL
  SELECT 'Mike', 'Chemistry', 30 FROM DUAL UNION ALL
  SELECT 'Ryan', 'Physics',   24 FROM DUAL UNION ALL
  SELECT 'Ryan', 'Bio',       24 FROM DUAL UNION ALL
  SELECT 'Ryan', 'Chemistry', 24 FROM DUAL;

Query 1:

select *
from   student_marks
where (Name,Subject) in (('Mike','Physics'),('Ryan',Subject))

Results:

| NAME |   SUBJECT | MARKS |
|------|-----------|-------|
| Mike |   Physics |    20 |
| Ryan |   Physics |    24 |
| Ryan |       Bio |    24 |
| Ryan | Chemistry |    24 |

Upvotes: 3

Related Questions