RuM3q9AA

1drop table if exists company;
2create table company
3(
4company_id int unsigned not null auto_increment primary key
5)engine = innodb;
6
7insert into company values (null), (null), (null);
8
9drop table if exists person;
10create table person
11(
12person_id int unsigned not null auto_increment primary key
13)engine = innodb;
14
15insert into person values (null), (null), (null), (null), (null);
16
17drop table if exists person_company;
18create table person_company
19(
20person_id int unsigned not null,
21company_id int unsigned not null,
22primary key (person_id, company_id)
23)engine = innodb;
24
25insert into person_company values 
26(1,1),(1,2),(1,3),
27(2,1),
28(3,2),(3,3),
29(4,2),
30(5,2),(5,3);
31
32
33select
34 p.person_id,
35 ifnull(ms.company_id,0) as 'microsoft',
36 ifnull(ora.company_id,0) as 'oracle',
37 ifnull(mysql.company_id,0) as 'mysql'
38from
39 person p
40left outer join person_company ms on p.person_id = ms.person_id and ms.company_id = 1
41left outer join person_company ora on p.person_id = ora.person_id and ora.company_id = 2
42left outer join person_company mysql on p.person_id = mysql.person_id and mysql.company_id = 3
43order by
44 p.person_id;
45
46
47-- or
48
49select
50 p.person_id,
51 if(ms.company_id is null,0,1) as 'microsoft',
52 if(ora.company_id is null,0,1) as 'oracle',
53 if(mysql.company_id is null,0,1) as 'mysql'
54from
55 person p
56left outer join person_company ms on p.person_id = ms.person_id and ms.company_id = 1
57left outer join person_company ora on p.person_id = ora.person_id and ora.company_id = 2
58left outer join person_company mysql on p.person_id = mysql.person_id and mysql.company_id = 3
59order by
60 p.person_id;