SQL problem

[mark_thurston]

I have the following 2 tables:

tempSearch
- ID
- Item
- Value

searchData
- Item
- Value
- Result

I need to go through the entries in tempSearch that have an ID of "1" and see which Result entries in searchData match all of the entries from tempSearch.

To clarify this a little, let me go through an example.

tempSearch has the following entries:
1 , 10 , "test"
1 , 11 , "testing"
1 , 12 , "test run"

searchData has the following entries:
10 , "test" , 3
11 , "testing" , 3
10 , "foo" , 4
11 , "bar" , 4
12 , "test run" , 4
10 , "test" , 5
11 , "testing" , 5
12 , "test runner" , 5
13 , "testing 1, 2, 3" , 5

Result 3 should not be returned even though it has a match on both Item 10 and 11, it lacks Item 12 from tempSearch.

Result 4 should not pass, it has Items 10, 11, and 12 but only Item 12 matches the entry in tempSearch.

Result 5 should be returned even though it has an extra Item. Item 12 matches the entry from tempSearch because we use the LIKE command.

Any help is greatly appreciated.

[YuckFou]

select s.Result from tempSearch t , searchData s
where t.ID = 1 AND s.Item = t.Item AND s.Value like '%'+t.Value+'%'
group by s.Result having count(*) = (select count(*) from tempSearch where ID = 1)

[skhanal]

This will not work if the count is 3 but rather than 3 different values it has duplicates.

In case of 5, it has 4 matching rows.

[YuckFou]

In case of duplicates I thought there is some unique key or index.

If there can be duplicates in column Value, I think it might work
with condition "having count(*) >= (select count(*) from tempSearch where ID = 1)"

In case of 5 there are only 3 matching rows because the 4th (testing 1, 2, 3) has Item 13 and this Item isn't in tempSearch.