using LIKE on another column

Question

In the table tags I have the field id. In the table tastings I have a field tags which is a list of id numbers separated by commas, for example 2,4,5. The database is MySQL.

Now I am trying to count how many times each tag is used in total. But I am stuck with the LIKE part. I have tried the following, all giving a syntax error:

SELECT tags.id, tag, FROM tags, tastings WHERE tags LIKE tags.id + '%'

SELECT tags.id, tag, FROM tags, tastings WHERE tags LIKE tags.id & '%'

SELECT tags.id, tag, FROM tags, tastings WHERE tags LIKE CONCAT(tags.id, '%')

What am I doing wrong?

Did you try this ? SELECT tags.id, tag, FROM tags, tastings WHERE tags LIKE '%' + tags.id + '%' — Baatar
– Baatar, Commented Jan 11, 2012 at 8:23
If I try the || I get weird results, it seems to be not the same as using CONCAT() — Niels
– Niels, Commented Jan 11, 2012 at 8:45

Bohemian · Accepted Answer · 2012-01-11 08:24:29Z

9

I helps if you post the error, but you have an extra comma after the select list. Also, you might need to qualify tags, since it is both a table and a column.

Try this:

SELECT tags.id, tag
FROM tags, tastings
WHERE tastings.tags LIKE CONCAT('%', tags.id, '%')

or better, use the new join syntax:

SELECT tags.id, tag
FROM tags
JOIN tastings on tastings.tags LIKE CONCAT('%', tags.id, '%')

Note the sandwiching of tag.id in % so you find the id anywhere in it.

Warning: This join will hit id 4 when tags are 13,14,15 (there's a 4 in 14), so unless your ids are all less than 10, you'll need to rethink your join criteria.

answered Jan 11, 2012 at 8:24

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Code Magician · Accepted Answer · 2012-01-11 08:23:33Z

1

You haven't specified your DBMS so I'm taking a guess at what is supported.

How about

SELECT tags.id, tastings.tag
FROM tags 
    INNER JOIN tastings  
        ON tastings.tags like '%' + tags.id + '%'

This should work (again, depending on your DBMS) but you should really normalize your data, this type of thing really isn't going to scale/perform well.

answered Jan 11, 2012 at 8:23

Code Magician

24.1k7 gold badges63 silver badges77 bronze badges

Comments

Benoit · Accepted Answer · 2012-01-11 08:36:04Z

1

Try the below query.

select t.id , count(t.id) from tag t , tastings tas where tas.tags like '%' ||t.id||'%'
group by t.id;

edited Jan 11, 2012 at 8:36

Benoit

79.7k24 gold badges226 silver badges241 bronze badges

answered Jan 11, 2012 at 8:34

Kakarot

4,2522 gold badges18 silver badges18 bronze badges

Collectives™ on Stack Overflow

using LIKE on another column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related