SQL to produce matches from a data table and a pattern table

Given a data table with columns id, color, size and a second table with rules/patterns so rule_id, color_condition and size_condition.

So a basic rule would be rule_id=1,Color=blue, size=Any or rule_id=2,Color=blue, size=15

How can I construct a SQL query that produces matches into a third table

For example for an entry in the data table id=1, color=blue, size=10 both rules would apply and therefor the matches table would cotain two entries

rule_id=1, entry_id=1
rule_id=2, entry_id=1

How To cycle through the patterns and how to construct the matching so that it can deal with wildcards or omit conditions if they are em开发者_如何学编程pty.

Please provide directions or keywords, I am ready to read.

Let's say you have the rule table :

Rule
Id--Color--Size
1 --blue -- null
2 --blue -- 15

And Entry table

Entry
Id--Color--Size
1 --blue -- 10

Put a null value instead of a 'Any' value to keep some strong typing

A solution :

Select r.id as rule_id,
       e.id as entry_id
From Entry e inner join Rule r
          On (e.Color = r.Color or r.Color is null)
          And (e.Size <= r.Size or r.Size is null)

You can create a new table Color for better performance :

Color
Id--Name
1 --Red
2 --Blue

Rule
Id--Id_Color--Size
1 --  2     -- null
2 --  2     -- 15

Entry
Id--Id_Color--Size
1 --   2    -- 10

Select r.id as rule_id,
       e.id as entry_id
From Entry e inner join Rule r
     On  (e.Id_Color = r.Id_Color or r.Color is null)
     And (e.Size <= r.Size or r.Size is null)

Add an index to both Id_Colors