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

Question

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?

possible duplicate of [Select Distinct value SQL](http://stackoverflow.com/questions/7979583/select-distinct-value-sql) — Jordan Kasper, Dec 06 '13 at 18:38

score 4 · Answer 1 · answered Dec 06 '13 at 18:19

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'

score 3 · Answer 2 · answered Dec 06 '13 at 18:19

3

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';

answered Dec 06 '13 at 18:19

Pablo Díaz Ogni

1,033
8
12

score 0 · Answer 3 · answered Dec 06 '13 at 18:20

0

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

answered Dec 06 '13 at 18:20

OpEx

1

score 0 · Answer 4 · edited May 23 '17 at 10:25

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.

Just a question, why do you add aliases with exactly same name as tables ? — Raphaël Althaus, Dec 06 '13 at 18:32
@RaphaëlAlthaus - not exact; i capitalized them, which denotes difference between alias and table. and to answer your question, its coding preference; on much larger view creation with multiple joins and a lot of fields / logic, having simple characters like `b` creates more work for QAs to test and post-prod enhancement developers to digest. this way is very transparent, but it makes no difference on performance. — PlantTheIdea, Dec 06 '13 at 18:36

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

4 Answers4