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?

Andrey · Accepted Answer · 2010-03-22 15:10:59Z

4

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

answered Mar 22, 2010 at 15:10

Andrey

60.3k12 gold badges127 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan · Accepted Answer · 2010-03-22 15:12:02Z

1

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;

answered Mar 22, 2010 at 15:12

Dan

5482 silver badges7 bronze badges

1 Comment

Guffa Over a year ago

That's basically what I was about to suggest, only a little less complicated. :)

Peter Mortensen · Accepted Answer · 2010-04-13 19:18:46Z

1

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;

edited Apr 13, 2010 at 19:18

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 22, 2010 at 15:38

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2010-04-13 19:23:10Z

0

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.

edited Apr 13, 2010 at 19:23

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 22, 2010 at 15:26

Kip Real

3,4994 gold badges23 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL simple query

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related