Article:
  SQL Subqueries
Subject:   subqueries
Date:   2003-05-15 11:32:27
From:   anonymous2
how do you do a subquery where you are using a composite primary key for example:


select courseID, studentID
from whatever
where courseID AND? StudentID IN (
select courseID, studentID
from somewhereelse
);

Main Topics Oldest First

Showing messages 1 through 2 of 2.

  • subqueries
    2008-12-30 00:50:08  my pages [View]

    i will send in next mail
  • subqueries
    2003-06-26 07:54:31  anonymous2 [View]

    You'd have to use the EXISTS operator instead of the IN operator for that, IN will only work with single column results:

    SELECT
    courseID
    , studentID
    FROM
    whatever W
    WHERE
    EXISTS (
    SELECT
    1 -- doesn't matter what you return here, even NULL is OK, the EXISTS operator is satisfied as soon as anything is returned
    FROM
    somewhereelse S
    WHERE
    S.courseID = W.courseID
    AND S.studentID = W.studentID
    )

    Another option would be to do an inner join on the two columns, but that's not a subquery so beyond the scope of this discussion :)