Sql Un-Wizardry: compare values from one list to another
I have a comparison that I would like to be more efficient in SQL.
The input field (fldInputField) is a comma separated list "1,3,4,5"
The database has a field (fldRoleList) that contains "1,2,3,4,5,6,7,8"
So, for the first appearance of fldInputField inside fldRoleList, tell us what value it was.
Is there a way to do the following in MySQL or a stored procedure?
pseudocode
SELECT * FROM aTable t1 WHERE fldInputField on t1.fldRoleList
/ pseudocode
I'm guessing there might be some features that work best for this type of comparison? I couldn't find anything in the search, if someone could direct me I will delete the question ... Thanks!
UPDATE: This is not a perfect (or good) way to do things. He inherited the code, and we're just trying to install a quick fix while we're looking at building the logic to deal with this through normalized strings .. Fortunately, this is not heavily used code.
source to share
I agree with @Ken White's answer that comma delimited lists have no place in normalized database design.
The solution will be easier and better if you have stored 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 MySQL function FIND_IN_SET()
)
But this will output multiple lines if multiple roles match a comma separated input string. If you need to limit the result to one line per record aTable
, with the first comparable 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;
source to share
You have a terrible circuit design, you know. Comma delimited lists have no business in the DB.
That said ... You are looking for LIKE.
SELECT * FROM aTable t1 WHERE t.fldRoleList LIKE fldInputField + '%'
If the content does not always match at the beginning, add another percent sign before fldInputField.
SELECT * FROM aTable t1 WHERE t.fldRoleList LIKE '%' + fldInputField + '%'
source to share