CODEWITHSUNDEEP
mysql
ab11
ab11

Reputation: 20090

Mysql, how to combine results from join queries?

I have these tables:

Objects
ID | Name
----------
1    name1

Tags
ID | ObjectId | Tag
--------------------
1    1          tag1
2    1          tag2

Attributes
ID | ObjectId | Key | Value
--------------------
1    1          key1  value1  
2    1          key2  value2

And I'd like to write a query which would give a single row like:

id = 1
name = name1
tags = tag1,tag2
attributes = key1=value1,key2=value2

However, this query:

SELECT      o.name, 
            GROUP_CONCAT(t.tag) as tags,
            GROUP_CONCAT(CONCAT(a,'=',a.value)) as attributes
FROM        objects o 
LEFT JOIN   attributes a on a.ObjectId = o.id
LEFT JOIN   tags t on t.ObjectId = o.id
WHERE       p.id = 1

gives me this single row result, with repeated tag/attribute values

id = 1
name = name1
tags = tag1,tag1,tag2,tag2
attributes = key1=value1,key2=value2,key1=value1,key2=value2

Upvotes: 0

Views: 22

Answers (1)

The Impaler
The Impaler

Reputation: 48770

The issue with your solution is that you are joining all related tags against all related attributes, effectively producing a cross join.

Instead, you could use scalar subqueries. For example, you can write it as:

select
  id,
  name,
  (SELECT GROUP_CONCAT(t.tag) FROM tags t on t.ObjectId = o.id) as tags,
  (SELECT GROUP_CONCAT(CONCAT(a.key,'=',a.value)) 
     FROM attributes a on a.ObjectId = o.id) as attributes
FROM objects o
WHERE id = 1

Upvotes: 1

Related Questions