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?