SQL query query

Question

SQL query to get list of Authors who have written at least a book on all Genres

Tables are

      Author_id  |  Author_info  => authors
      Genre_id   |  Genre_info   => genres
      Author_id  |  Genre_id  |  Book_id  => books

I'm using Mysql, what would be the best way to do this?

Have you tried something yourself already? As a tip, start by finding authors where there is a gender that they have NOT written about. Those are the authors that you DON'T want. — njr101
– njr101, Commented Nov 18, 2011 at 9:19
My idea was counting the number of genres from 'genres' and then matching them with distinct mapping of (author_id,genre_id), but as I have been away from SQL for a while, I couldn't put it together in SQL — theReverseFlick
– theReverseFlick, Commented Nov 18, 2011 at 9:25
This is a good approach as well and it will probably be faster. The answer from Thilo is nice and does exactly this. — njr101
– njr101, Commented Nov 18, 2011 at 9:49
yes it is what i could do in English but needed someone like Thilo to put down as SQL — theReverseFlick
– theReverseFlick, Commented Nov 18, 2011 at 9:52

Thilo · Accepted Answer · 2011-11-18 09:19:28Z

2

If N is the number of distinct genres:

select author_id, count(distinct genre_id) as genres
from books 
group by author_id 
having genres = N

answered Nov 18, 2011 at 9:19

Thilo

17.8k5 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Utku Yıldırım · Accepted Answer · 2011-11-18 10:14:41Z

1

Assumed database schema as this

authors
|-  Author_id  -|- Author_info -|
genres
|-  Genre_id   -|- Genre_info  -|
books
|-  Author_id  -|-  Genre_id   -|-  Book_id -|

Query

SELECT authors.Author_id
FROM authors
LEFT INNER JOIN books ON (authors.Author_id = books.Author_id)
GROUP BY authors.Author_id
HAVING COUNT(DISTINCT books.Genre_id) = (SELECT COUNT(*) FROM genres)

edited Nov 18, 2011 at 10:14

answered Nov 18, 2011 at 9:27

Utku Yıldırım

2,27716 silver badges20 bronze badges

1 Comment

njr101 Over a year ago

Are you sure this will work? Looking at the SQL I think it will aggregate the count across all books for all authors as there is no grouping by author.

Sudhir Bastakoti · Accepted Answer · 2011-11-18 09:27:58Z

0

Try this:

SELECT Author_id
FROM books
INNER JOIN Genre USING (Genre)
GROUP BY Author_id
HAVING COUNT(Author_id) >= (SELECT COUNT(*) FROM Genre);

answered Nov 18, 2011 at 9:27

Sudhir Bastakoti

100k15 gold badges163 silver badges169 bronze badges

Comments

Priyank_Vadi · Accepted Answer · 2016-12-02 06:22:14Z

0

For written at least a book on all Genres

Try...

select author_id, count(distinct genre_id) as genres
from books 
group by author_id 
having genres >= 1

answered Dec 2, 2016 at 6:22

Priyank_Vadi

1,14811 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL query query

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related