1

I have 2 tables

table 1 - apps

-----------
id  | name
-----------
1   | abc
-----------
2   | sdf
-----------
3   | dfg
-----------

table 2 - contacts

------------------------
id  | cnt_type  | cnt_id
------------------------
1   | manager   |    20
------------------------
1   | supervisor |  32
------------------------
1   | teamlead   | 45
------------------------
1   | teamlead  | 54
------------------------
2   | manager    |   20
------------------------
2   | supervisor |  32
------------------------

I need a view as below where cnt_type rows are represented as columns and the corresponding values are concatenated and group by the cnt_type per id...

---------------------------------------
id  | manager | supervisor  | teamlead
--------------------------------------
1   | 20      |    32       | 45, 54
--------------------------------------
2   | 20      |    32       |
--------------------------------------

Any help is greatly appreciated.

  • How many cnt_type should be on table contacts? – oNare Aug 5 '15 at 19:20
0

PROPOSED QUERY

SELECT
    A.id,
    IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
FROM apps A
LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
GROUP BY A.id;

SAMPLE DATA

DROP DATABASE IF EXISTS kumar;
CREATE DATABASE kumar;
USE kumar
CREATE TABLE apps
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE contacts
(
    id INT NOT NULL,
    cnt_type VARCHAR(20) NOT NULL,
    cnt_id INT NOT NULL
);
INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
(1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
(1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS kumar;
Query OK, 2 rows affected (0.05 sec)

mysql> CREATE DATABASE kumar;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar
Database changed
mysql> CREATE TABLE apps
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE contacts
    -> (
    ->     id INT NOT NULL,
    ->     cnt_type VARCHAR(20) NOT NULL,
    ->     cnt_id INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
    -> (1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
    -> (1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

SAMPLE DATA IN TABLES

mysql> select * from apps;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | sdf  |
|  3 | dfg  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from contacts;
+----+------------+--------+
| id | cnt_type   | cnt_id |
+----+------------+--------+
|  1 | manager    |     20 |
|  1 | supervisor |     32 |
|  1 | teamlead   |     45 |
|  1 | teamlead   |     54 |
|  2 | manager    |     20 |
|  2 | supervisor |     32 |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     A.id,
    ->     IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    ->     IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    ->     IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
    -> FROM apps A
    -> LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
    -> LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
    -> LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
    -> WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
    -> GROUP BY A.id;
+----+---------+------------+----------+
| id | manager | supervisor | teamlead |
+----+---------+------------+----------+
|  1 | 20      | 32         | 45,54    |
|  2 | 20      | 32         |          |
+----+---------+------------+----------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

  • Works like a charm, thanks Rolando for the quick reply, I had been scratching my head last couple of hours. – Kumar Aug 5 '15 at 19:41
  • What do i need to do to include all rows from apps table. With the query above, i am able to retrieve all the rows from contacts table that have a matching row in apps table. – Kumar Aug 5 '15 at 19:43
  • If you want all apps to appear, even if there are no contacts, simply remove WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3. – RolandoMySQLDBA Aug 5 '15 at 19:46
  • Awesome, you are the BEST. I found a new friend on this site. – Kumar Aug 5 '15 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.