adding the value of a column into a LIKE statement?

Question

hi there

i have 3 tables for tags, tag categories and used tags. i'm wanting to get a list of all tags with a count of the used tags (the format of the tags used is comma separated values for each document id that has tags).

i've been trying something like this but I can't get the value of the tags.tag field to be inserted into the LIKE statement.

SELECT categories.category, tags.tag,
(SELECT COUNT(id) FROM usedtags WHERE usedtags.value LIKE '%tags.tag%') as numtags
FROM tags
LEFT JOIN categories ON categories.id = tags.category_id
ORDER BY categories.category, tags.tag

There must be a better way to do this but my SQL is not good enough!!

Can someone please point me in the right direction?

Many thanks

Answer 1

Something like this, perhaps:

WHERE [column] LIKE [OtherColumn] + '%'

I also want to point out a major performance problem in your current plan. You're trying to use an initial wild card in your search, like this: LIKE '%tags.tag%'. That is very bad, because it doesn't work with your indexes. We would say that it's not sargable. Any time you find yourself wanting to use an initial wild card like that, you should look at a full-text index instead. It's not that hard and will dramatically improve your database performance.

Answer 2

maybe something like this:

SELECT c.Category, t.tags, count(u.id)
FROM Tags T
Left Join Categories c on c.id = t.category_id
Inner Join UsedTags u on u.value like '%' & t.tag & '%'

the & may need to be replaced by your specific database (for instance in T-SQL it's the double pipe ||)