SQL Server 2005 -Join Based on Criteria in a Table Column

Question

Using SQL Server 2005, what is the most efficient way to join two tables in the following scenario?

The number of entries in each table can be quite large, approximately 200,000.

The only way I can do this now is to use cursors and some dynamic SQL for each element, which will obviously be very inefficient.

I have two tables - the PERSON table and the SEARCHITEMS table. The SEARCHITEMS table contains a column with some simple criteria that should be used when matching records with the PERSON table. Criteria can refer to any column in the PERSON table.

For example, the following tables:

PERSON table

 PERSONID FIRSTNAME LASTNAME GENDER AGE ... VARIOUS OTHER COLUMNS 1 Fred Bloggs M 16 .... 200000 Steve Smith M 18 

SEARCHITEMS table

 ITEMID DESCRIPTION SEARCHCRITERIA 1 Males GENDER = 'M' 2 Aged 16 AGE=16 3 Some Statistic {OTHERCOLUMN >= SOMEVALUE AND OTHERCOLUMN < SOMEVALUE} .... 200000 Males Aged 16 GENDER = 'M' AND AGE = 16 

RESULTS table should contain something like the following:

 ITEMID DESCRIPTION PERSONID LASTNAME 1 Males 1 Bloggs 1 Males 200000 Smith 2 Aged 16 1 Bloggs .... 200000 Males Aged 16 1 Bloggs 

It would be nice to be able to do something like

 INSERT INTO RESULTSTABLE SELECT * FROM PERSON P LEFT JOIN SEARCHITEMS SI ON (APPLY SI.SEARCHCRITERIA TO P) 

But I do not see a way to do this work. Any help or ideas appreciated.

Answer 1

Seeing that the SEARCHITEMS table is not relational, it seems that the cursor and the dynamic SQL solution are the only ones that work. Of course, it will be rather slow, and I would “pre-calculate” the results to make it somewhat bearable.

To do this, create the following table:

 CREATE TABLE MATCHEDITEMS( ITEMID int NOT NULL CONSTRAINT fkMatchedSearchItem FOREIGN KEY REFERENCES SEARCHITEMS(ITEMID), PERSONID int CONSTRAINT fkMatchedPerson FOREIGN KEY REFERENCES PERSON(PERSONID) CONSTRAINT pkMatchedItems PRIMARY KEY (ITEMID, PERSONID) ) 

There will be a lot of data in the table, but considering that it only stores 2 columns, the disk size on the disk will be small.

To update this table, you create triggers:

• a trigger in the SEARCHITEMS table that will populate the MATCHEDITEMS table whenever a rule is changed or added.
• a trigger in the PERSON table that will run the rules for updated or added PERSON records.

Results can simply be presented by combining three tables.

 SELECT m.ITEMID, m.DESCRIPTION, m.PERSONID, p.LASTNAME FROM MATCHEDITEMS m JOIN PERSON p ON m.PERSONID = p.PERSONID JOIN SEARCHITEMS s ON m.ITEMID = s.ITEMID 

Answer 2

You can build your TSQL dynamically and then execute it with sp_executesql .