SELECT with LIKE and LEFT JOIN

Question

Hello, I’m trying to get records based on the name of the stone and the tags.

Tables:

** Tabela tag **
----------------
id       nome
----------------
1        Sem categoria
2        cs
3        weapon
4        balrog


** Tabela tag_relac **
----------------------
id_tag       id_pedra
----------------------
1        50
1        51
1        60
2        60
3        60
4        60    


** Tabela pedras **
-------------------
id         nome
-------------------
50        Teste
51        Teste 2
60        Balrog-1 Blue Explosion   

If I set the name of the stone, it works.

SELECT pedras.nome, GROUP_CONCAT(DISTINCT tag.nome ORDER BY tag.nome ASC SEPARATOR ', ') AS tags FROM pedras LEFT JOIN tag_relac ON pedras.id = tag_relac.id_pedra LEFT JOIN tag ON tag_relac.id_tag = tag.id WHERE pedras.nome LIKE '%balrog%' OR tag.nome LIKE '%weapon%' GROUP BY pedras.id ORDER BY pedras.nome ASC

Now, if I set the name of the tag, it only returns what is related to the name.

SELECT pedras.nome, GROUP_CONCAT(DISTINCT tag.nome ORDER BY tag.nome ASC SEPARATOR ', ') AS tags FROM pedras LEFT JOIN tag_relac ON pedras.id = tag_relac.id_pedra LEFT JOIN tag ON tag_relac.id_tag = tag.id WHERE pedras.nome LIKE '%teste%' OR tag.nome LIKE '%weapon%' GROUP BY pedras.id ORDER BY pedras.nome ASC

Any idea?

Answer 1

If that’s what I’m thinking is because of the case sensitive... You’re searching for the "%test% stone" but in the bank the names are as "Test".... the first search returned right because coincidentally the name of the stone you were looking for was in the tag that matched what you typed in the like of the tag