MYSQL - Join using LIKE

Trying to join tables using a LIKE clause

[joedub]

Hello,

Ok so this post isn't exactly a PHP problem but i couldn't find a MySql forum section...

The below query worked fine, it joined all of the programs to the category they belonged to, and counted how many programs belonged to each category.

SELECT a.CatID, a.CatParentID, a.CatName, b.ProgCat, count(b.ProgLive) AS prog_count
FROM categories a
LEFT JOIN programs b
ON b.ProgCat=a.CatID
GROUP BY a.CatID
ORDER BY a.CatParentID ASC, a.CatName ASC

But when i had to change the relationship where a program could belong to multiple categories, i tried to use the same query, but join the tables using a LIKE statement instead. The mysql manual says that tables can be joined using ON, with query syntax that can be used in a WHERE clause, so i tried this below...

SELECT a.CatID, a.CatParentID, a.CatName, b.ProgCat, count(b.ProgLive) AS prog_count
FROM categories a
LEFT JOIN programs b
ON b.ProgCat LIKE '%_'+ a.CatID +'_%'
GROUP BY a.CatID
ORDER BY a.CatParentID ASC, a.CatName ASC

This time round i changed the ProgCat field from an int field, to a var char, and the CatIDs that the program belongs to are stored as a string, with an underscore either side. e.g '_1_', or '_1__2_'. ITs as if the query is ignoring the like statement and performing a cross join, matching every record in 1 table to every record in the other.

Any help much appreciated.

Cheers

[joedub]

ON b.ProgCat LIKE CONCAT( '%_', a.CatID, '_%' )

seemed to do the trick...

[coopster]

yeah, I catch myself now and then using "+" as a concatenation operator in different languages and/or SQL syntax by accident too, joedub.

I read the following once:

Whenever you are in SQL, use CONCAT. Other operators, including the vertical bar (¦) character might inhibit code portability. It is preferable to use the CONCAT operator instead of the concatenation operator (¦¦). Use of the vertical bar should be avoided because it is a variant character.

and in the same documentation:

Using the vertical bar (¦) character might inhibit code portability between relational database products. Use the CONCAT operator in place of the ¦¦ operator. On the other hand, if conformance to SQL 1999 Core standard is of primary importance, use the ¦¦ operator).

hehe, don't you just love stuff like that? Well, the SQL 1999 standard does indeed direct us to use the ¦¦ operator. Choose your poison, eh ;-)

Personally, I stick to standards but I often use a layer to keep my code portable, meaning if I am really concerned about it I'll use some form of function or abstraction layer to make portability changes easier.

The forum breaks the pipe symbol. Any reference to the pipe symbol in this message is not a broken pipe.