SQL join two rows based on an id

Question

I am trying to basically get all the tags for a specific merchant, and I am hoping to have one row per merchant with all the tags (see below for what I'm getting with the current query and what I'm hoping to get). One merchant could have a bunch of tags, but each row for Tag only has one merchant. Also merchants and tags are associated through a table merchanttags which just holds the id of the tag and the id of the merchant.

Query

select m.MerchantId, m.MerchantName, t.TagName
from Merchant m 
inner join MerchantTags mt on m.MerchantId=mt.MerchantId 
inner join tag t on mt.TagId=t.TagId 
where m.MerchantId=162

Result

MerchantId | MerchantName | TagName 
162        |   merchant   |  tag1
162        |   merchant   |  tag2

Desired Result

MerchantId | MerchantName | TagName | TagName  
162        |   merchant   |  tag1   | tag2

Please tag your question with the database you are actually using. MySQL and SQL Server are different databases. — Gordon Linoff, Jul 16 '15 at 18:56
So, if Merchant 162 has two tags and Merchant 163 has 4 tags, what does the desired output look like? Does the table have 1, 2 or 4 tag columns? What is the maximum number of tags a merchant can have? What should the output for Merchant 164 with just one tag look like? The column names should be unique, too. You might be looking for GROUP_CONCAT; you can search on SO for solutions using that. — Jonathan Leffler, Jul 16 '15 at 18:57
using SQL Server you will need to turn this into a pivot table — kya, Jul 16 '15 at 18:58
Its mySQL. Tagname column has 20 different values. The desire output would be that if merchant x has 15 tags then the row would have 15 tag columns, thanks ill look into GROUP_CONCAT — Billy, Jul 16 '15 at 19:01
You don't want 15 tags in 15 columns; it will be an all round nightmare. It will be a nightmare to build the query result; it will be a nightmare to make use of it. — Jonathan Leffler, Jul 16 '15 at 19:03
You are looking for something similar to this: http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — kya, Jul 16 '15 at 19:08

score 1 · Accepted Answer · answered Jul 16 '15 at 19:07

I would potentially use GROUP_CONCAT for this:

SELECT
  m.MerchantId AS MerchantId,
  m.MerchantName AS MerchantName,
  GROUP_CONCAT(t.TagName) AS MerchantTags
FROM Merchant m 
INNER JOIN MerchantTags mt
  ON m.MerchantId=mt.MerchantId 
INNER JOIN tag t
  ON mt.TagId=t.TagId 
WHERE m.MerchantId=?
GROUP BY m.MerchantId

This would give output like:

MerchantId | MerchantName | MerchantTags
162        | merchant     | tag1,tag2

You can easily explode the tags with whatever application language you are using and this gives you a fixed number of fields to work with rather than trying to create a pivot-table-type of output which may have unknown number of columns.

SQL join two rows based on an id

1 Answers1