SQL IN clause with multiple parameters

Question

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?

MT0 · Accepted Answer · 2017-09-21 11:46:24Z

3

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 |

answered Sep 21, 2017 at 11:46

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

SQL IN clause with multiple parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related