SELECT with LIKE and LEFT JOIN

0

Hello, I'm trying to fetch 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 stone name, 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 the one relative 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 ideas?

    
asked by anonymous 17.11.2017 / 15:49

1 answer

0

If that's what I'm thinking, it's because of the case sensitive ... You're looking for the stone like "% test%" but in the database the names are like "Test" .... the first search returned right coincidentally the name of the stone that you were looking for was in the tag that hit with what you typed in the like of the tag

    
17.11.2017 / 23:54