CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
user2531590
user2531590

Reputation:

SQL statement WHERE clause condition with multiple values

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.

Upvotes: 1

Views: 27127

Answers (2)

Ewan
Ewan

Reputation: 1087

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);

Upvotes: 0

nobalG
nobalG

Reputation: 4620

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

Upvotes: 4

Related Questions