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 !!!
cnt_type
should be on table contacts? – oNare Aug 5 '15 at 19:20