Women in Technology

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 );

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 :)