SQL Query help

[Subhash]

Hello,
I have a table as shown below. I need to write a sql query that tells
me what courses are common between Tom and Jim.

Name CourseID
-------------
Tom 1
Tom 2
Tom 3
Jim 1
Jim 3
Jim 4


Output needs to something like:

CourseID
--------
1
3

Appreciate all your help.
Thanks, Subhash

[Erland Sommarskog]

Re: SQL Query help

[posted and mailed, please reply in news]

Subhash (subhash_daga@y ahoo.com) writes:[color=blue]
> I have a table as shown below. I need to write a sql query that tells
> me what courses are common between Tom and Jim.
>
> Name CourseID
> -------------
> Tom 1
> Tom 2
> Tom 3
> Jim 1
> Jim 3
> Jim 4
>
>
> Output needs to something like:
>
> CourseID
> --------
> 1
> 3[/color]

SELECT tom.CourseID
FROM tbl tom
JOIN tbl jim ON tom.CourseID = jim.CourseID
WHERE tom.Name = 'Tom'
AND jim.Name = 'Jim'


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Shervin Shapourian]

Re: SQL Query help

Try this:

select CourseID
from Course
where Name in ('Tom', 'Jim')
group by CourseID
having count(*) > 1

Shervin

subhash_daga@ya hoo.com (Subhash) wrote in message news:<fdffa0fb. 0409090549.76e1 e44a@posting.go ogle.com>...[color=blue]
> Hello,
> I have a table as shown below. I need to write a sql query that tells
> me what courses are common between Tom and Jim.
>
> Name CourseID
> -------------
> Tom 1
> Tom 2
> Tom 3
> Jim 1
> Jim 3
> Jim 4
>
>
> Output needs to something like:
>
> CourseID
> --------
> 1
> 3
>
> Appreciate all your help.
> Thanks, Subhash[/color]