
Comparrison
I have a table of Tests. <BR><BR>tblTests<BR>TestName, TestResult<BR><BR>Each Test has a potential one to many relationship with "Requirements."<BR><BR>tblRequirements<BR>Test ID, RequirementName, RequirementValue<BR><BR>Now I programmatically receive a list of these Name Value pairs from an external device, and I need to find a test that matches EXACTLY the requirements I received. <BR><BR>For example:<BR><BR>I have 3 tests in the queue. <BR><BR>Test1 requires: <BR><BR>name  value<BR><BR> w  5<BR> x  4<BR> y  99<BR> z  "cde"<BR><BR>Test2 Requires<BR><BR>name  value<BR><BR> x  922<BR> y  2<BR><BR>Test3 Requires<BR><BR>name  value<BR><BR> u  1<BR> v  2<BR> w  3<BR> x  4<BR> y  5<BR> z  "abc"<BR><BR>Device sends:<BR><BR>name  value<BR><BR> x  4<BR> y  99<BR> z  "cde"<BR><BR><BR>The device may send more than x, y and z, or it may send less. A test may require more than x, y and z or it may require less. How can I write a query to find a perfect match? <BR><BR><BR>I hope this was clear. Thanks in advance.<BR>Kevin

I presume...
...that you are trying to discover *WHAT* test, if any, is matched by what the device sends?<BR><BR>In your example, I would assume that *NONE* of the tests match the device's set of values?<BR><BR>If the number of values from the device do not agree with the number of values in the test is that, too, a mismatch?<BR><BR><BR>

Correct< eop >
All of your assumptions, as usual, are quite correct.

Correct< eop >
All of your assumptions, as usual, are quite correct.

actually...
I am wrong in one aspect, the number of values from the device can be greater than the number required by the test, as long as the tests requirements are all met by the device.

Ugh...too bad...
...it isn't the other way around!<BR><BR>Hmmm...have to think on this one!<BR><BR>SELECT T.* FROM tblTests AS T, <BR> tblRequirements AS R1,<BR> tblRequirements AS R2,<BR> tblRequirements AS R3<BR>WHERE T.testID = R1.testID AND R1.name = 'x' AND R1.value = 4<BR> AND T.testID = R2.testID AND R2.name = 'y' AND R1.value = 99<BR> AND T.testID = R3.testID AND R3.name = 'z' AND R1.value = 'cde'<BR> <BR>And then do <BR> SELECT Count(R.testid) FROM tblTests AS T, tblRequirements AS R<BR> WHERE T.testID = R.testID<BR> AND T.testID = [value of T.testID from first query]<BR><BR>And if the COUNT you get from second query is the same as the record count from the first query, you've got it.<BR><BR>???<BR><BR>The tricky part is building up the joins to tblRequirements the number of times to match name/value pairs from the device.<BR><BR>Also, although you show your name value pairs as sometimes name/number and sometimes name/string, in truth they will ALL have to be of the same type!<BR><BR><BR> <BR><BR><BR><BR>

I see a potential problem...
Suppose that you have:<BR><BR>Test 17:<BR> x  '78'<BR> y  '99'<BR> z  'abc'<BR><BR>Test 23:<BR> a  'nice'<BR> x  '78'<BR> y  '99'<BR> z  'abc'<BR><BR>And the device also has:<BR> x  '78'<BR> y  '99'<BR> z  'abc'<BR><BR>So it ends up matching *BOTH* of those tests on the first query. And then you'd have to check both against the second query to make sure the count of 3 matches.<BR><BR>I guess we need to wrap it up into a single query, after all.<BR><BR>SELECT T.* FROM tblTests AS T, <BR> tblRequirements AS R1,<BR> tblRequirements AS R2,<BR> tblRequirements AS R3<BR>WHERE T.testID = R1.testID AND R1.name = 'x' AND R1.value = '4'<BR> AND T.testID = R2.testID AND R2.name = 'y' AND R1.value = '99'<BR> AND T.testID = R3.testID AND R3.name = 'z' AND R1.value = 'cde'<BR> AND 3 = (SELECT Count(R.testid) FROM tblTests AS T2, tblRequirements AS R<BR> WHERE T2.testID = R.testID<BR> AND T2.testID = T.testID) <BR><BR>Or something similar to that.<BR><BR><BR>

Access didn't like that...
...last line of the test, where I compared 3 to the count(*), but it might be okay in SQL Server.<BR><BR>The two query scheme would work fine so long as no test is ever a proper subset of another test.<BR><BR><BR>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

