How to use JOIN and avoid getting duplicate data?

Question

I have 3 tables: projects, categories and projects_categories.

I want to get the project name, description and categories but there can be more than one category per project so i am getting duplicate content using the next query:

SELECT projects.name, projects.description, categories.name
FROM projects
JOIN projects_categories ON projects.project_id = projects_categories.project_id
JOIN categories ON projects_categories.category_id = categories.category_id
WHERE projects.project_id = ?

I am getting this:

++ name +++   ++ description ++    ++ name (category) ++
project1      bla bla bla          film
project1      bla bla bla          music

As u can see im getting two rows instead of one

score 2 · Accepted Answer · answered Dec 16 '11 at 21:42

2

Group_concat is the tag that you are looking for:

SELECT projects.name, 
       projects.description, 
       group_concat(categories.name SEPARATOR ' ') as category_name
FROM projects
JOIN projects_categories 
  ON projects.project_id = projects_categories.project_id
JOIN categories 
  ON projects_categories.category_id = categories.category_id
WHERE projects.project_id = ?
group by projects.name, projects.description

answered Dec 16 '11 at 21:42

Brian Hoover

7,861
2
28
41

Great! But lets say I want to get each category separated? Is there a 'mysql way' or just use split() or something like that will be better? – Jonathan Dec 16 '11 at 21:49
@Jonathan - You pretty much need to use the equivalent of split() in your programming language, assuming that any project can be assigned to n categories. If you limit the number of categories a project can belong to, you could hard code each of those categories into the query. – Brian Hoover Dec 16 '11 at 21:51

score 2 · Answer 2 · answered Dec 16 '11 at 21:42

2

You could use GROUP_CONCAT to combine the categories and return a single row.

SELECT projects.name, projects.description, GROUP_CONCAT(categories.name)
FROM projects
JOIN projects_categories ON projects.project_id = projects_categories.project_id
JOIN categories ON projects_categories.category_id = categories.category_id
WHERE projects.project_id = ?
GROUP BY projects.name, projects.description

answered Dec 16 '11 at 21:42

Joe Stefanelli

132,803
19
237
235

Great! But lets say I want to get each category separated? Is there a 'mysql way' or just use split() or something like that will be better? – Jonathan Dec 16 '11 at 21:49
Separated as in separate columns in one row? – Joe Stefanelli Dec 16 '11 at 21:51
@Jonathan: See this Q&A for an example. [Join two tables (with a 1-M relationship) where the second table needs to be 'flattened' into one row](http://stackoverflow.com/questions/5826455/join-two-tables-with-a-1-m-relationship-where-the-second-table-needs-to-be-fl) – Joe Stefanelli Dec 16 '11 at 22:02

How to use JOIN and avoid getting duplicate data?

2 Answers2