Multiple Search Conditions - One table

Hi all,
I'm looking for some suggestions on this one.
I have a table of Contacts and CustomFields. I have a join table called Cus
tomFieldsContacts. The DDL is listed below, along with my question.
CREATE TABLE CustomFields (
CustomFieldID int NOT NULL IDENTITY,
CustomFieldName nvarchar(50) NOT NULL,
CONSTRAINT PK_CustomFieldID PRIMARY KEY (CustomFieldID)
)
GO
CREATE TABLE Contacts (
ContactID int NOT NULL IDENTITY,
ContactName nvarchar(50) NOT NULL,
CONSTRAINT PK_ContactID PRIMARY KEY (ContactID)
)
GO
CREATE TABLE CustomFieldsContacts (
CustomFieldID int NOT NULL,
ContactID int NOT NULL,
Value nvarchar(100) NOT NULL,
CONSTRAINT PK_CustomFieldsContacts PRIMARY KEY (CustomFieldID, ContactID)
)
GO
ALTER TABLE CustomFieldsContacts
ADD Constraint FK1_CustomFieldsContacts_CustomFields FOREIGN KEY (CustomFiel
dID)
REFERENCES CustomFields(CustomFieldID)
GO
ALTER TABLE CustomFieldsContacts
ADD Constraint FK2_CustomFieldsContacts_Contacts Foreign key (ContactID)
REferences Contacts(ContactID)
GO
insert contacts values ('Frogger')
insert contacts values ('LeapFrog')
insert contacts values ('Lazy Dog')
insert contacts values ('Brown Fox')
insert contacts values ('DooDah')
insert customfields values ('FavoriteColor')
insert customfields values ('FavoriteNumber')
insert customfields values ('MotherMaidenName')
insert customfieldscontacts values (1, 1, 'Blue')
insert customfieldscontacts values (1, 2, 'Blue')
insert customfieldscontacts values (1, 3, 'Green')
insert customfieldscontacts values (1, 4, 'Brown')
insert customfieldscontacts values (2, 1, '42')
insert customfieldscontacts values (2, 2, '27')
insert customfieldscontacts values (2, 3, '11')
insert customfieldscontacts values (2, 4, '12')
insert customfieldscontacts values (3, 1, 'Kermit')
insert customfieldscontacts values (3, 2, 'Bambi')
insert customfieldscontacts values (3, 3, 'Big Bird')
insert customfieldscontacts values (3, 4, 'Foxy')
insert customfieldscontacts values (3, 5, 'Blue')
DROP TABLE CustomFieldsContacts
DROP TABLE Contacts
DROP TABLE CustomFields
==========================
I would like to be able to dynamically create queries that can search on mul
tiple values within the join table. Note: We have no problem altering the t
able structures if necessary.
Example query:
Get all contacts where FavoriteColor Like 'B%'
AND MotherMaidenName Like 'K%'
Other Notes: We will have as many as 100 custom fields and as many as 500,0
00 contacts.
Any suggestions on how to architect this would be helpful.
Thank you!
Rick Sawtell
MCT, MCSD, MCDBA"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uQBzsNN0EHA.1308@.TK2MSFTNGP09.phx.gbl...
Hi all,
I'm looking for some suggestions on this one.
I have a table of Contacts and CustomFields. I have a join table called
CustomFieldsContacts. The DDL is listed below, along with my question.
SNIP
You can use your application to build a dynamic WHERE clause and send
that to a stored proc for execution.
So your app might pass something the looks like:
"(CustomFieldID = 1 and Value = 'abc') AND (CustomFieldID = 3 and Value
= '123')"
In your SP, you can use dynamic SQL (you'll need to grant select access
to the underlying table(s) to all your users). Just make sure you code
against possible SQL Injection issues.
Create Proc DynTest -- untested
@.nvcWhere as nvarchar(2048)
as
begin
Declare @.nvcSQL nvarchar(4000)
Set @.nvcSQL = N'Select c.ContactID, c.ContactName From Contacts INNER
JOIN CustomFieldsContacts WHERE ' + @.nvcWhere
Exec sp_executesql @.nvcSQL
end
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uPf76bP0EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Rick Sawtell" <quickening@.msn.com> wrote in message So your app might
> pass something the looks like:
> "(CustomFieldID = 1 and Value = 'abc') AND (CustomFieldID = 3 and Value =
> '123')"
>
Thanks for your feedback David, however your proposed solution will not
work. I can do the dynamic SQL part. I was more interested in how to
generate the query itself.
In the above example that you gave, you will never get any records back.
When a WHERE clause is running it is operating on a single row of data at a
time. Because of this and the way the table is designed, you will *never*
have a single row that has both a CustomFieldID of 1 AND a CustomFieldID of
3.
If you put an OR between the two expressions, then you would get data back
for both expressions. The problem I am having is when a single user record
needs to have expressions or constraints placed on multiple CustomFields.
In order for your solution to work, I would need to do a join on the data.
That way a row could have CustomFieldID =1 and a CustomFieldID = 3 in the
same row, in which case, a WHERE clause could apply.
The problem with the joins is that with an unlimited number of custom fields
and having to perform a join for each custom field, the query quickly
expands in size to the point that it is not efficient.
So anyone else have any ideas? I was thinking about possibly using a star
schema in DSS?!?
Rick Sawtell
MCT, MCSD, MCDBA|||I've been thinking more about your problem.
- You have a many to many table on a variable number of custom fields
and corresponding values.
- You want to know how to best query those tables given that the user of
application can construct dynamic conditions for the query on any number
of custom fields
"Writing" out loud here, how about we try the following:
- Add a column to the CustomFields table called Rank. It's a tinyint
that determines how selective the custom field is. Say a value of 1 is
not selective at all and a lot of Contacts are likely to have it and a
value of 5 is highly selective.
- Assume all the conditions are ANDed together (ORing is going to
require a different algorithm)
- Pass in two varchar(2000) or nvarchar parameters of a length that's
likely to fit all values for all parameters: @.CustomFieldIDs and
@.CustomFieldValues. The parameters are comma delimited or tab delimited.
Whatever works. For each ID in @.CustomFieldIDs there is a corresponding
value in @.CustomFieldValues
- Create a temp table in the database to store working results for this
procedure. Create it with the columns that are to appear in the final
result set. Call it #CustomResults
- Loop through both parameter lists and store the results in a 3 column
temp table (CustomFieldID, CustomFieldValue, Rank). After the id and
values are inserted, update the table with the value for the rank from
the CustomFields table
- Using a cursor, loop through the temp table. Order the results by the
Rank DESC so the more selective custom fields are at retrieved first
- Grab the CustomFieldID and CustomFieldValue and execute an INSERT INTO
#CustomResults SELECT * FROM on the main tables using the bound
parameters for ID and Value. No dynamic SQL is necessary. Only do the
insert the first time, so use a bit flag so subsequent iterations in the
loop do not insert again
- For the remaining rows in the cursor, we are going to do deletes from
the #CustomResults table, using: Delete From #CustomResults Where
ContactID NOT IN (Select ContactID from tables where @.CustomFieldID and
@.CustomFieldValue)
- For each iteration of the loop, check to make sure the temp table has
rows. If not kill the loop and get out because there are no matches.
- When the loop completes, you should have a list of customers in the
#CustomResults that meet the condition.
David Gugick
Imceda Software
www.imceda.com