1

I was having some problem when trying to write a SQL statement with multiple values for WHERE clause condition. So basically I will draw a buffer on the map and it will return me a list of eventID.

So I created this SQL statement and tested it:

SELECT count(*) AS totalRaces, a.races FROM bookedevent be INNER JOIN 
account a 
ON be.bookedEventBY = a.accountName 
WHERE be.eventID = 70 OR be.eventID = 69 OR be.eventID = 55
GROUP BY a.races

It works. But then again, this SQL statement only works for 3 eventID. For some buffer it might return me up to 10 eventID. So how should I format it in this case?

Thanks in advance.

Improve this question
1
  • 1
    doesn't look like you did any research on this one, just as a note, a simple google search with the title of your question would return the correct answer for you Commented Jan 21, 2015 at 7:35

2 Answers 2

Reset to default
4

Use IN clause

SELECT count(*) AS totalRaces, a.races FROM bookedevent be INNER JOIN 
account a 
ON be.bookedEventBY = a.accountName 
WHERE be.eventID in(70,69,55)
GROUP BY a.races
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You Need to use dynamic SQL.

Then you can pass your list of eventIDs as a comma separated list. You can then dynamically vary the number of items in the IN cluse

    declare @Events as varchar(1000)
    declare @SQL as varchar(1000)

    Select @Events = '55, 60, 70'

    select @SQL = 'SELECT count(*) AS totalRaces, a.races 
    FROM bookedevent be 
    INNER JOIN account a ON be.bookedEventBY = a.accountName 
    WHERE be.eventID in (' + @Events + ')
    GROUP BY a.races'

    Exec (@SQL);
Improve this answer

2 Comments

This is a lousy use case for a dynamic query, it's not needed here.
I just used it as an example of how you could use dynamic SQL to solve such problems. I appreciate that it is overkill