CODEWITHSUNDEEP
mysqlsql
Billy
Billy

Reputation: 853

SQL join two rows based on an id

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

Upvotes: 0

Views: 82

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

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.

Upvotes: 1

Related Questions