CODEWITHSUNDEEP
mysqlsql
Brododipollo
Brododipollo

Reputation: 85

AND operator between some columns of one table

With a serious of join, I have this table, in witch the rows are the name of a author, the title of his book, and the id of the book. If a book has two or more authors, there are two or more rows.

+------------+------+--------+
| AuthorName | book | idBook |
+------------+------+--------+
| a1         | b1   |      1 |
| a2         | b1   |      1 |
| a3         | b2   |      2 |
| a4         | b2   |      2 |
| a5         | b2   |      2 |
+------------+------+--------+

Now I want to create a filter in witch if a user insert the authors, he'll get the title of a combinations of the inserted authors. For example: If I insert "a1" and "a2", I'll get b1. But if I insert "a1" and "a3", I'll get a empty string, because there isn't a combination between this two authors.

How can I write a query for this problem? I've tried something like:

select AuthorName, book, idBook
from table
where AuthorName = a1 and AuthorName = a2

But obviously it doesn't work because the result is a empty string. Any suggestions?

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use aggregation:

select book, idBook
from t
where AuthorName in ('a1', 'a2')
group by book, idbook
having count(*) = 2;  -- number of elements in the list

Note: You should not be storing book in this table. bookid should be a foreign key reference to a table, such as books, where bookid is the primary key.

Upvotes: 2

Related Questions