CODEWITHSUNDEEP
mysqlsql
KeySee
KeySee

Reputation: 780

How to remove duplicates from table join in SQL

I have set up a little directory website for client.

For this question we need to know at least those two:

   TABLE: places                       TABLE: tags

   | pid | name    | lat  | lng  |     | tid | pid | value |
   -------------------------------     ---------------------
   |  1  | Place 1 | x.xx | x.xx |     |  0  |  2  | tag1
   |  2  | Place 2 | x.xx | x.xx |  +  |  1  |  2  | tag2
   |  3  | Place 3 | x.xx | x.xx |     |  2  |  1  | tag1

... I want to get something more like this

   | pid | name    | lat  | lng  | value      |
   --------------------------------------------
   |  2  | Place 2 | x.xx | x.xx | tag1, tag2 | <3    
   |  1  | Place 1 | x.xx | x.xx | tag1       |

... and not something like this (thats a join)

   | pid | name    | lat  | lng  | value      |
   --------------------------------------------
   |  2  | Place 2 | x.xx | x.xx | tag1       | 
   |  2  | Place 2 | x.xx | x.xx | tag2       |              
   |  1  | Place 1 | x.xx | x.xx | tag1       |

Is it possible to merge different tags like above with pure SQL?

Upvotes: 0

Views: 36

Answers (3)

Akshay Patil
Akshay Patil

Reputation: 249

Use Inner join

select p.pid,p.name,p.lat,p.long,group_concat(t.value) as value from places p inner join tags t on p.pid=t.pid group by p.pid,p.name,p.lat,p.long

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You just want group by and group_concat(). However, because you want all the columns from places I would use a subquery:

select p.*,
       (select group_concat(t.value)
        from tags t
        where t.pid = p.pid
       ) as `values`
from places p;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You just need GROUP_CONCAT after joining the tables.

select p.pid,p.name,p.lat,p.long,group_concat(t.value) as value
from places p
join tags t on p.pid=t.pid
group by p.pid,p.name,p.lat,p.long

Upvotes: 2

Related Questions