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