CODEWITHSUNDEEP
mysql
user3075483
user3075483

Reputation: 1

MySQL: how to get result from 2 tables without repeating results?

I've got 3 tables: book, publisher, book_category

For a particular book category (fantasy) I have to display list of publisher names supplying that genre.

publisher_name and category_name are linked through book table, so my query is:

SELECT publisher.publisher_name
FROM publisher, book, book_category
WHERE publisher.publisher_id = book.publisher_id
AND book.category_id = book_category.category_id
AND category_name =  'fantasy';

But the result I'm getting is repeating the name of publisher if there's more than one fantasy book supplied by that publisher.

Let's say I've got The Hobbit and The Lord of the Rings,both are fantasy and are supplied by the same PublisherA. In that case the result of my query is:

PublisherA

PublisherA

Is it possible to get that result just once? Even if there's much more than 2 fantasy books published by the same publisher?

Upvotes: 0

Views: 78

Answers (4)

PlantTheIdea
PlantTheIdea

Reputation: 16359

Everyone is mentioning DISTINCT, which is correct (better than GROUP BY in MySQL, because of the way the optimizer is set up), but I figured I would also add a modification for performance enhancements.

Currently you have implicit cross joins to get to the other tables, and making these explicit INNER JOINs will increase efficiency because of the order of filtering. Example:

SELECT DISTINCT Publisher.publisher_name
FROM publisher Publisher
INNER JOIN book Book ON Publisher.publisher_id = Book.publisher_id
INNER JOIN book_category Book_Category ON Book.category_id = Book_Category.category_id
WHERE Book_Category.category_name = 'fantasy';

In the original query, you bring in the complete record set of all three tables (publisher, book, book_category), and then from that set you join on the respective keys, and then return the result set. In this new query, your join to Book_Category happens based only upon the record set returned from the join between Publisher and Book. If there is filtering that happens based on this join, you will see a performance increase.

You also have the added benefit of being ANSI-compliant, as well as explicit coding to improve ease of maintenance.

Upvotes: 0

OpEx
OpEx

Reputation: 1

Try adding this to the end of the query: GROUP BY publisher.publisher_name

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Just use distinct if you only need publisher_name

SELECT distinct publisher.publisher_name

by the way, try to use JOIN syntax... to join tables

SELECT distinct p.publisher_name
FROM publisher p
join book b on b.publisher_id = p.publisher_id
join book_Category bc on bc.category_id = b.category_id
where bc.category_name = 'fantasy'

Upvotes: 4

Pablo Díaz Ogni
Pablo Díaz Ogni

Reputation: 1043

Use DISTINCT

SELECT DISTINCT publisher.publisher_name
FROM publisher, book, book_category
WHERE publisher.publisher_id = book.publisher_id
AND book.category_id = book_category.category_id
AND category_name = 'fantasy';

Upvotes: 3

Related Questions