CODEWITHSUNDEEP
mysqlsql
dev.nikolaz
dev.nikolaz

Reputation: 3184

Duplicate fields in col query result

I have 3 tables with books, authors and genres

I want to get output result as: book, author and genres, but when I do this query, I have duplicate fields, if books have 5 genres in example first row of query result) author row duplicates 5 too or reverse.

Example on SQL Fiddle here:

http://sqlfiddle.com/#!2/a15d8/1

I need to in first row:

book | one author | five genres ... etc.

Upvotes: 0

Views: 58

Answers (2)

Arun
Arun

Reputation: 951

To get what you want, add DISTINCT to line 4, and you won't have any duplicates. However, you may still return more than one author (unique), but that's what your query should return the way it is.

like: 

group_concat(DISTINCT concat_ws (' ',`a`.`last_name`,`a`.`first_name`,`a`.`middle_name`)) as author,

Upvotes: 1

newBee
newBee

Reputation: 1319

You should reconsider your schema. Just some thoughts to share: (I know this does not answer your question but trust me: using a good schema from the beginning can save you a lot of work later)

  1. In your books table use the author.id as foreign key. Otherwise you will have trouble as soon as you get duplicate names (which is possible). Joining those tables will also become ways easier.

  2. You will most likely have a certain set of genres and will assign them to your books. This is a n:m-relationship. Meaning one book can have multiple genres and one genre can be assigned to multiple books. You will need a relationship table to do this. The Relationship table will then contain the ID of a book and the ID of a genre and link them.

Upvotes: 0

Related Questions