SEO

#1 (**permalink**) 02-29-2008, 03:37 AM

I have 3 tables:

1. tblSports which has an ID and Sport columns (basketball, baseball,
football, and volleyball)

2. tblTeams with an ID and Team columns (TeamsA to D)

3. tblJoin which has two foreign key columns (ID for Sports, ID for
Teams) This is where teams are joined to whatever Sports they play.

Questions:

1. How should I do my select statement when given the team
it'll show the sports they do NOT play?

2. Just the opposite of the 1st. Given the sport, what teams do NOT
play?

Thanks in advance for your help.
Marvin

#2 (**permalink**) 02-29-2008, 03:37 AM

SELECT S.sport_id, S.sport_name
FROM tblSports AS S
WHERE NOT EXISTS
(SELECT *
FROM tblJoin
WHERE sport_id = S.sport_id
AND team_id = ???)

SELECT T.team_id, T.team_name
FROM tblTeams AS T
WHERE NOT EXISTS
(SELECT *
FROM tblJoin
WHERE team_id = T.team_id
AND sport_id = ???)

--
David Portas
SQL Server MVP
--

#3 (**permalink**) 02-29-2008, 03:38 AM

It WORKS!
Thanks a lot.

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

SEO

SQL many-to-many query problem. HELP !