SQL simple query

Question

I have the following table, Persons_Companies, that shows a relation between persons and companies knowns by these persons:

PersonID  |  CompanyID

   1             1
   2             1
   2             2
   3             2
   4             2

Imagining that company 1 = "Google" and company 2 is = "Microsoft", I would like to know the query to have the following result:

PersonID  |  Microsoft    |    Google

   1             0                1
   2             1                1
   3             1                0
   4             1                0

Until this moment I have something similar:

select PersonID,
case when CompanyID=1 then 1 else 0
end as Google,
case when EmpresaID=2 then 1 else 0
end as Microsoft
from Persons_Companies

My problem is with the persons that knows both companies, I can't imagine how could this query be.

What is the SQL query?

Answer 1

select PersonID,
case when EXISTS (
   SELECT 1 
   FROM Persons_Companies pc1 
   WHERE pc.PersonID = pc1.PersonID and pc1.CompanyID = 1 ) then 1 else 0
end as Google,
case when EXISTS (
   SELECT 1 
   FROM Persons_Companies pc2
   WHERE pc.PersonID = pc2.PersonID and pc2.CompanyID = 2 ) then 1 else 0
end as Microsoft
from Persons_Companies pc

Answer 2

SELECT personId, sum(case companyId when 1 then 1 else 0 end) google,
        sum(case companyId when 2 then 1 else 0 end) microsoft
from Persons_Companies
group by personId
order by personId;

Answer 3

There is a problem with both answers, because there is an assumption that Google and Microsoft will always be the only companies on the table. I believe the query should be generic.

I am not too sure but I think a combination of a cross tab and CTE will work well.

Answer 4

I think this is what you want: http://pastie.org/881092

select
 p.person_id,
 if(ms.company_id is null,0,1) as 'microsoft',
 if(ora.company_id is null,0,1) as 'oracle',
 if(mysql.company_id is null,0,1) as 'mysql'
from
 person p
left outer join person_company ms on p.person_id = ms.person_id and ms.company_id = 1
left outer join person_company ora on p.person_id = ora.person_id and ora.company_id = 2
left outer join person_company mysql on p.person_id = mysql.person_id and  mysql.company_id = 3
order by
 p.person_id;