Search code examples
sqlsql-server-2008sql-like

SQL: Use one Table as Input for a 'Like' Statement on Another Table

I have a sql database with unique IDs and competition names, as such:

ID     CompetitionName
1      August Running Race
2      Running Race August
3      Running Race August
4      Running Race August.
5      Cycling Race
6      Cycling Race September

Notice even when CompetitionName is identical, each row gets it's own ID.

I then have a file to consolidate these competition names into reasonable groups, using rules. Rules can be either 'equals' or 'contains'. Contains rules mean that if the CompetitionName from the above table contains what is under 'RuleA' and what is under 'RuleB' (if RuleB isn't blank) in any order, then we should take that competitionName and ID for our new table and under a third column (Competition) should put the competition entry from below. For equals rules the title must match RuleA exactly.

RuleType   RuleA           RuleB     Competition
Contains   Running Race    August    August Running Race
Equals     Cycling Race              September Cycling Race

So the output I would like here is:

ID     CompetitionName      Competition
1      August Running Race  August Running Race
2      Running Race August  August Running Race
3      Running Race August  August Running Race
4      Running Race August. August Running Race
5      Cycling Race         September Cycling Race

Cycling Race September is not included in this new table because it did not match any of the rules.

In the code I currently have, I am using like statements to do one rule at a time:

select ID
from table1
where 
(CompetitionName LIKE '%Running Race%' and CompetitionName LIKE '%August%')

However, I would like to take these rules automatically from a table rather than typing them out, and to also take the corresponding Competition entry from the second table when a rule applies, so we have that standard competition name for these entries. How would I go about this problem? If you have any ideas for solving just part of it, please let me know too, as it is something to build on.

Solution

  • Assuming you have a rules table in the database, you can do

    select t.ID, t.CompetitionName, r.competition
from table1 t, rules  
where CompetitionName like 
(case when ruletype = 'Contains' then '%' + RuleA + RuleB + '%' end)
or CompetitionName in (case when ruletype = 'Equals' then RuleA end)

    However, this would not be the ideal approach and you should focus on getting the table design corrected.