MYSQL search in fields with equal names

Asked

Viewed 53 times

1

Hello, I need to do a search in mysql in which I use several Internet joins, and because it is many relationships for many, some of the fields relate to one more field of the other table, follows an example where it will be easier to understand

SELECT livros.id_livro, livros.nome, livros.caminho, categorias.nome as categoria FROM livros INNER JOIN categorizados ON categorizados.id_livro = livros.id_livro INNER JOIN categorias ON categorizados.id_categoria = categorias.id_categoria WHERE categorias.nome LIKE 'Terror' AND categorias.nome LIKE 'Suspense'

Here I highlight the following excerpt: WHERE categorias.nome LIKE 'Terror' AND categorias.nome LIKE 'Suspense' And it is about him that the doubt is related, in one of my registered books, I have one that has two categories in it registered, one with the name 'Terror' and the other with the name 'Suspense' But I can not filter this book with this request, I believe there is some way to make this request, but I do not know how yet, I would like you to help me, thank you!

Edit1: The search after the WHERE are in different records, one record has the name field as 'Terror' and the other record has the name field as 'Suspense'

php html mysql

  • If I understood you should not use OR in place of AND since, as far as I understand, a field cannot have both the values 'Terror' and 'Suspense'. It may be that if you want to select those that have two records in the table categories you can use the usual GROUP BY with COUNT and the clause HAVING COUNT(id_category) > 1.

  • These are different records, the two tables are related, and I have two records, one with the field name as 'Terror' and another with the field name as 'Suspense'

1 answer

1

I think what you want is something like:

SELECT livros.id_livro, livros.nome, livros.caminho, GROUP_CONCAT(categorias.nome SEPARATOR ';') as categoria 
FROM livros INNER JOIN categorizados ON categorizados.id_livro = livros.id_livro 
            INNER JOIN categorias ON categorizados.id_categoria = categorias.id_categoria 
WHERE categorias.nome LIKE 'Terror' OR categorias.nome LIKE 'Suspense'
GROUP BY livros.id_livro
HAVING count(categorias.id_categoria) > 1

  • Manin, at Where I need you to have an AND between the two conditions, not an OR

  • My dear, if you have in each record of your table categories a name field that contains a text ('Terror', 'Suspense', etc.) then, from what I understand from your description, it is impossible for each record to contain different names, which is the meaning of the AND. From what you described it is not a record with 'Terror' another record with 'Suspense' and so on?

  • Look, I don’t think you understand what I meant, but what I have is a table called categories as you might have noticed, and inside it I have a column called one has within the column name 'Terror' and the other 'Suspense', note that both share the same table, and same column, but different records, so the request for both is categories.name And then I have a table (books) that relates to this table through an intermediary (categorized), in summary is not only 1 record, but 2

  • Sorry if I expressed myself badly before which has resulted in misunderstanding, but is that I am still a little beginner

  • Test the query I presented and make sure that the displayed result is not exactly what you want. Study the HAVING clause as it will determine which books are classified in Terror and in Suspense. Utilise OR because they are in different records.

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.