CODEWITHSUNDEEP
mysqlsqlsetselection
Long N
Long N

Reputation: 21

Set then Select

Can somebody please help me with this simple (but I don't know how) task?

In order to avoid wasting time fixing every single line, I'm trying to do something like this

    SET @auditor = a, b, c;

    SELECT *
    FROM Audits
    WHERE auditor in (@auditor)

My query indeed is really long and this part "WHERE auditor in (@auditor)" will be repetitive. Therefore, doing some kind of Set first will make it faster to update when there are some changes in the work force (we hire "d" and/or "b" left)

Thanks in advance

Upvotes: 0

Views: 59

Answers (3)

user2314737
user2314737

Reputation: 29307

You can use an array variable and FIND_IN_SET

SET @auditors = 'a,b,c';

SELECT *
FROM Audits
WHERE FIND_IN_SET(auditor,@auditors)

Explanation: FIND_IN_SET(auditor,@auditors) is true is auditor is found in @auditors and in this case it returns the index.

But I would consider saving the auditors in a separate table rather than in a variable to better keep track of them.

Upvotes: 0

RJ_
RJ_

Reputation: 3

you cannot set a parameter like an array. you will have to do something like

set @auditor='a,b,c'

now if auditor is a number this should work but if it is anything other then you will have to create a funciton dbo.[tfn_SplitList] on your system you will use it like below

auditor in(select List from dbo.[tfn_SplitList](@auditor ,','))

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

I recommend using a temporary table at the top of your query instead:

CREATE TEMPORARY TABLE FilteredAudits AS (SELECT * FROM Audits WHERE Auditor IN (a,b,c))

Then replace Audits with FilteredAudits as needed.

The benefits to this approach is that you guarantee the filter will only be applied once, meaning the rest of the query will JOIN to the smallest data set possible, and you don't have to repeat your IN() filter throughout the query.

Upvotes: 1

Related Questions