Help with range of texts against range of texts count query

[th1nk1nk]

I am trying to design a query that will return how many times my first tables texts occur in a range of texts in a second table.



Static texts table named "vals" looks like below and there are many rows that will stay the same
tex

*01*02*
*02*03*
*03*04*

and my second table "data" is a range of values that will change from time to time looking something like this
dats

01-02-03
01-02-03-04-05
01-02-09-28-100
02-03-100
01-02-100
01-02-09


So from what is listed above im hoping that the query will end up displaying
tex counts
*01*02* 5
*02*03* 3
*03*04* 1

so each vals.tex(non repeating, constant) values are getting checked against all of the data.dats (subject to change)

i've been using instr and count but i cant seem to make up a sql statement/query correctly. In excel i would do the same using countif. thanks for any help you can provide in what the query/sql statement would look like.

[weekend00]

select vals.tex, count(*) from vals left join data on data.dats like vals.tex group by vals.tex

Please be sure tex in vals are unique.

Notice: you patterns are not very good because they will count the records which you don't want, e.g:
pattern: *01*02* will match 01-03-102

[th1nk1nk]

Ok, that seems to get me close to what i wanted and i see how the data becomes an issue.

ive reformed what i have and now i'm seeing with that query i still have leaks.

with

ID tex
101 *01*02*03*04*06*
102 *01*02*03*04*07*
103 *01*02*03*04*08*
104 *01*02*03*04*09*
105 *01*02*03*04*10*
106 *01*02*03*04*11*
107 *01*02*03*04*12*

and

ID dats
2 01-02-03-04-05-06-07-08-09-10
3 01-02-03-04-05-06-07-08-09-10
4 01-02-03-04-05-06-07-08-09-10
5 01-02-03-04-05-06-07-10-22-40
6 01-02-03-04-05-06-29-30-35-40
7 01-02-03-04-05-06-09-41-46-55
8 01-02-03-04-05-06-07-08-09-10
9 01-02-03-04-05-06-07-08-09-10
10 01-02-04-05-06-07-08-09-10-11
11 01-02-05-06-07-08-09-10-11-12
12 01-02-05-06-07-10-11-12-13-14



i seem to be getting a 1 on results that are not existing. Is there anyway to return 0 for these records. I'm assuming this is because on the join it has to be counted even if it doesn't exist. Also, is it possible to alias the count so that i could then sort the results largest to smallest.

I would also like to be able to eventually cut the query down by the dats side by adding a date time and filtering the query against it. for exampe
instead of running the query against all the rows run it against only the top 10 records or between 5/16/10-5/17/10. More important is just how to place a stipulation on the dats side because its easier for me to place all my information in and then cut it down on the query.