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):
A1-Previous Exhibitor 1 year
A2-Previous Exhibitor 2 years
AA-Exhibitor 1 booth
AB-Exhibitor 2 booth
I would like the result for this example data to be:
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:
Any help you can give is greatly appreciated!