Sql Un-Wizardry: Compare values from one list in another

Question

I have a comparison I'd like to make more efficient in SQL.

The input field (fldInputField) is a comma separated list of "1,3,4,5"

The database has a field (fldRoleList) which contains "1,2,3,4,5,6,7,8"

So, for the first occurrence of fldInputField within fldRoleList, tell us which value it was.

Is there a way to achieve the following in MySQL or a Stored Procedure?

pseudo-code

SELECT * FROM aTable t1 WHERE fldInputField in t1.fldRoleList

/pseudo-code

I'm guessing there might be some functions that are best suited for this type of comparison? I couldn't find anything in the search, if someone could direct me I'll delete the question... Thanks!

UPDATE: This isn't the ideal (or good) way to do things. It's inherited code and we are simply trying to put in a quick fix while we look at building in the logic to deal with this via normalized rows.. Luckily this isn't heavily used code.

Answer 1

You have a terrible schema design, you know. Comma-delimited lists have no business in a DB.

That being said... You're looking for LIKE.

SELECT * FROM aTable t1 WHERE t.fldRoleList LIKE fldInputField + '%'

If the content might not always match at the beginning, add another percent sign before fldInputField.

SELECT * FROM aTable t1 WHERE t.fldRoleList LIKE '%' + fldInputField + '%'

Answer 2

I agree with @Ken White's answer that comma-delimited lists have no place in a normalized database design.

The solution would be simpler and perform better if you stored the fldRoleList as multiple rows in a dependent table:

SELECT t1.*, r1.fldRole 
FROM aTable t1 JOIN aTableRoles r1 USING (aTable_id) 
WHERE FIND_IN_SET(r1.fldRole, fldInputField);

(see the MySQL function FIND_IN_SET())

But that outputs multiple rows if multiple roles match the comma-separated input string. If you need to restrict the result to one row per aTable entry, with the first matching role:

SELECT t1.*, MIN(r1.fldRole) AS First_fldRole 
FROM aTable t1 JOIN aTableRoles r1 USING (aTable_id) 
WHERE FIND_IN_SET(r1.fldRole, fldInputField);
GROUP BY t1.aTable_id;