CODEWITHSUNDEEP
mysqlsql
PHP Ferrari
PHP Ferrari

Reputation: 15616

MySQL: Concatenate different values as single value

In mysql db table I have projects with one to many relation with domain table like

projects

----------------
proId | domainId
----------------
  1        1
  1        2
  2        1
  3        3

domain

---------------------
domainId | domainName
---------------------
    1        Web
    2        Mobile
    3        iPhone

I query

SELECT p.*, d.* FROM projects p LEFT JOIN domain d ON p.domainId = d.domainId

which results

result

-----------------------------
proId | domainId | domainName
-----------------------------
  1       1          Web
  1       2          Mobile
  2       1          Web
  3       3          iPhone

but is it possible to show all domains as single value with concatenation some thing like

-----------------------------
proId | domainId | domainName
-----------------------------
  1       1, 2    Web, Mobile
  2       1       Web
  3       3       iPhone

Upvotes: 1

Views: 82

Answers (3)

PHP Ferrari
PHP Ferrari

Reputation: 15616

For quick question I just posted major points now as by help of friends it is solved & now i am posting complete DB Schema & Query.

Schema

CREATE TABLE projects
    (`projectId` int, `projectName` varchar(20))
;

INSERT INTO projects
    (`projectId`, `projectName`)
VALUES
    (1, 'P1'),
    (2, 'P2'),
    (3, 'P3')
;

CREATE TABLE domain
    (`domainId` int, `domainName` varchar(6))
;

INSERT INTO domain
    (`domainId`, `domainName`)
VALUES
    (1, 'Web'),
    (2, 'Mobile'),
    (3, 'iPhone')
;

CREATE TABLE prodomain
    (`domainId` int, `projectId` int)
;

INSERT INTO prodomain
    (`domainId`, `projectId`)
VALUES
    (1, 1),
    (1, 1),
    (3, 2),
        (2, 3)
;

Query

    SELECT p.projectId as proId, projectName, d.*, 
GROUP_CONCAT(d.domainId separator ', ') as all_domains_id, 
GROUP_CONCAT(d.domainName separator ', ') as all_domains_name 
FROM projects p 
    LEFT JOIN projectdomains pd ON p.projectId = pd.projectId
    LEFT JOIN domains d ON d.domainId = pd.domainId
    GROUP BY p.projectId

Upvotes: 1

John Woo
John Woo

Reputation: 263693

probably you are looking for GROUP_CONCAT function.

SELECT  a.proID,
        GROUP_CONCAT(b.domainID) domainId,
        GROUP_CONCAT(b.domainName) domainName
FROM    projects a
        LEFT JOIN domain b
            ON a.domainID = b.domainID
GROUP BY a.proID

SQLFiddle Demo

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

SELECT 
    p.proId, 
    group_concat(d.domainId) as domainId, 
    group_concat(d.domainName) as domainName 
FROM 
    projects p 
    inner JOIN 
    domain d ON p.domainId = d.domainId
group by p.proId
order by p.proId

Upvotes: 0

Related Questions