Geek asks and answers

SQL to capture similar rows with different identifiers

I have a table with:

term_id    course_id

1          592
1          603
2          592
2          603
2          700

How can I create a query to select ALL course_ids, which are in terms of 1 and 2?

+3
source share
4 answers
SELECT course_id
FROM T 
WHERE term_id=1
INTERSECT
SELECT course_id
FROM T 
WHERE term_id=2

or

SELECT course_id
FROM T 
WHERE term_id IN (1,2)
GROUP BY course_id
HAVING COUNT(DISTINCT term_id) = 2
+8
source 
SELECT * INTO #TEMP1
FROM T
WHERE TERM_ID = 1

SELECT * INTO #TEMP2
FROM T
WHERE TERM_ID = 2

SELECT DISTINCT COURSE_ID
FROM #TEMP1
WHERE COURSE_ID IN (SELECT DISTINCT COURSE_ID FROM #TEMP2)
+1
source

Martin's answers are concise and smart, but I will add two more approaches that might be easier to analyze, depending on what you usually read.

Without any assumptions about your scheme:

SELECT DISTINCT
  course_id
FROM
  MyTable AS T1
WHERE
      term_id = 1
  AND EXISTS (SELECT * FROM MyTable AS T2 WHERE T1.course_Id = T2.course_id AND T2.term_id = 2)

If, as I believe, {course_id, term_id} is unique, you can also do this:

SELECT
  T1.course_id
FROM
  MyTable AS T1
  INNER JOIN MyTable AS T2 ON T1.course_id = T2.course_id
WHERE
      T1.term_id = 1
  AND T2.term_id = 2
+1
source

More generally, to get all the courses in several terms:

select course_id
from foo
group by course_id
having count(term_id) > 1
+1
source

More articles:


All Articles