SQLTeam.com | Weblogs | Forums

Need a count of partial matches


#1

I have two tables. One table contains the distinct codes with descriptions. The other table has a field that contains multiple codes in one field. I can count how many of a specific code the second table contains. I can also count how many exact matches there are of the distinct codes, but I can't count if the second table only contains the code.

Table data examples (fields separated dashes):
Table: Exhibitor_Type
Headers: Code-Description
A1-Previous Exhibitor 1 year
A2-Previous Exhibitor 2 years
AA-Exhibitor 1 booth
AB-Exhibitor 2 booth

Table: Exhbitor_Info
Headers: ID-Exhib_Contact_Type
1234-A1,AB
2345-A1,AA
3456-A2,AB
4567-A1

I would like the result for this example data to be:
A1-3
A2-1
AA-1
AB-2

Doing this:
select g.CODE, count(e.Exhib_Contact_Type) AS "TYPE COUNT" from dbo.Exhibitor_Type t
join dbo.Exhibitor_Info e ON t.CODE = e.Exhib_Contact_Type
where e.EX_CONTACT_TYPE like ('%' + CODE + '%')
group by t.CODE
order by t.CODE

This only gets me the count of the exact matches. It ignores the '%'. I've tried putting in t.code in the where clause. I've tried adding square brackets around CODE in the where clause. My current results would look like this with the example data:

A1-1
A2-0
AA-0
AB-0

Any help you can give is greatly appreciated!

Danielle


#2

If you would post useable data -- i.e. CREATE TABLE and INSERT statements with the actual data -- then we could help with us. The actual code to do this is not all that difficult.