Filtering in Oracle based on a group of values contained in a list of values

I have following two tables:

ID_PERSON    NAME
-----------------
    1        John
    2        Joe
    3        Peter

ID_PERSON    ID_SPECIALIZATION
------------------------------
    1               5
    1               6
    1               7
    2               5
    2               1
    3               6
    3              10

I need to filter data based on group of ids ID_SPECIALIZATION that will be provided. For example I want to display only those persons who has specialization in 5 and 6 so it will return only first person. In ASP.NET Web form there will be two listboxes, left and right button, in first LB there will be all possible specializations and user wil开发者_JS百科l choose some of them to second LB as filtering options. I have no idea how to put this filtering condition in sql query. Thanks for help.

You could use the following:

SQL> SELECT p.id_person, p.NAME
  2    FROM person p
  3    JOIN person_spe s ON p.id_person = s.id_person
  4   WHERE id_specialization IN (5, 6)
  5   GROUP BY p.id_person, p.NAME
  6  HAVING COUNT(*) = 2;

 ID_PERSON NAME
---------- -----
         1 John

One way to do it:

SELECT 
    ID_PERSON
  , NAME
FROM 
    Person AS p
WHERE EXISTS
        ( SELECT *
          FROM
              PersonSpecialization AS ps
          WHERE ps.ID_PERSON = p.ID_PERSON
            AND ps.ID_SPECIALIZATION = 5
        )
  AND EXISTS
        ( SELECT *
          FROM
              PersonSpecialization AS ps
          WHERE ps.ID_PERSON = p.ID_PERSON
            AND ps.ID_SPECIALIZATION = 6
        )

SELECT d1.id_person, d1.name FROM tbl_table1 d1
INNER JOIN tbl_table2 d1
ON d1.ID_PERSON=d2.ID_PERSON
WHERE ID_SPECILIZATION = ?

Theres the query but I'm not sure how asp.net works and passing in the value. It might be work looking up bind variables which allows you to use place holders in the sql which oracle then caches the query and just uses the values that you pass in at run tuime using EXECUTE IMMEDIATE.