[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

Subquery has multiple values to compare to in the Where clause

I have a database table that contains race result information.

Table: Results
Name      Track      RaceDate      EventType      RaceNumber
Banana      PA      2008-01-01       A      1
Orange      PA      2008-01-01       A      1
Blue      PA      2008-01-01       A      1
Purple      OK      2008-02-01       A      2
Lemon      MA      2008-02-01       A      2
Peanut      PA      2008-02-01      A      2
Blue      PA      2008-02-01      A      2
+250,000 more

My user selects a random assortment of races that we need to analyze.  All of the choices are inserted into a 'temporary' database table for later use.

Table: TemporaryRaceResults
Track      RaceDate      EventType      RaceNumber
PA      2008-01-01      A      1
PA      2008-01-01      A      2
PA      2008-02-01      A      1
PA      2008-02-01      A      5
+ 20 or so more

When the SQL script is run, I want to return an unique list of names of participants in the races.

Although it doesn't work too badly, I don't want to use a cursor if I can help it.
One reason is that it doesn't properly return a unique list of names, but rather is full of duplicates.
Is there a way in T-SQL to return just what I want?

DECLARE @track char(2)
DECLARE @raceDate smalldatetime
DECLARE @eventType char(1)
DECLARE @raceNumber tinyint

DECLARE MY_CURSOR Cursor FAST_FORWARD FOR
SELECT Track, RaceDate, EventType, RaceNumber FROM TemporaryRaceResults;

OPEN MY_CURSOR

FETCH NEXT FROM MY_CURSOR INTO @track, @raceDate, @eventType, @raceNumber

WHILE @@FETCH_STATUS = 0
BEGIN

      SELECT DISTINCT Name FROM Results
      WHERE Results.Track = @track
        AND Results.RaceDate = @raceDate
        AND Results.EventType = @eventType
        AND Results.RaceNumber = @raceNumber

      FETCH NEXT FROM MY_CURSOR INTO @track, @raceDate, @eventType, @raceNumber
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Thanks for your time!
0
psdavis
Asked:
psdavis
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You can use a simple INNER JOIN.
SELECT DISTINCT r.Name
FROM Results r
INNER JOIN TemporaryRaceResults t
ON t.track = r.track AND t.racedate = r.racedate
AND t.eventtype = r.eventtype AND t.racenumber = r.racenumber

Open in new window

0
 
psdavisAuthor Commented:
Ok, I just wAyayyyyyyy overthought this one.

Thanks for the quick answer mwvisa1!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now