SEO

#1 (**permalink**) 02-29-2008, 04:12 AM

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

#2 (**permalink**) 02-29-2008, 04:13 AM

[posted and mailed, please reply in news]

Subhash (subhash_daga@yahoo.com) writes:
> 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

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@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#3 (**permalink**) 02-29-2008, 04:13 AM

Try this:

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

Shervin

subhash_daga@yahoo.com (Subhash) wrote in message news:<fdffa0fb.0409090549.76e1e44a@posting.google. com>...
> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL Query help