Mysql Select query: Select employees that their position are both CEO and Owner

Question

I have a below Table Diagram which consists mainly of 4 Tables, I would like to retrieve the company_name and person_name which is their position (CEO and Owner) of the same company.

Companies Table
People Table
Positions Table
Company_people table that combines the above three tables using foreign keys

Here are my Mysql Tables:

    #
# Structure for table "companies"
#

DROP TABLE IF EXISTS `companies`;
CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

#
# Data for table "companies"
#

INSERT INTO `companies` VALUES (1,'Apple'),(2,'Microsoft'),(3,'Tesla'),(4,'SpaceX');

#
# Structure for table "company_person"
#

DROP TABLE IF EXISTS `company_person`;
CREATE TABLE `company_person` (
  `cp_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `position_id` int(11) NOT NULL,
  PRIMARY KEY (`cp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

#
# Data for table "company_person"
#

INSERT INTO `company_person` VALUES (1,1,13,1),(2,1,13,2),(3,2,12,2),(4,2,12,1),(5,4,11,2),(6,4,11,1),(7,3,11,1),(8,3,11,2),(9,1,14,3),(10,2,16,3),(11,3,17,4),(12,4,20,3),(13,4,17,3),(14,2,18,3),(15,3,18,2),(16,4,17,2),(17,4,17,4),(18,1,12,2),(19,3,12,2),(20,4,12,1);

#
# Structure for table "people"
#

DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

#
# Data for table "people"
#

INSERT INTO `people` VALUES (11,'Elon Mask'),(12,'Bill Gates'),(13,'Steve Jobs'),(14,'Azad Omer'),(15,'Johney Deep'),(16,'Brad Pitt'),(17,'Jeff'),(18,'Zukerberg'),(19,'Will Smith'),(20,'Rapar');

#
# Structure for table "position"
#

DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

#
# Data for table "position"
#

INSERT INTO `position` VALUES (1,'Owner'),(2,'CEO'),(3,'Stake Holder'),(4,'Third Party');

Now, I would like to retrive company_name, person_name that are thier position are (CEO and Owner) of the same company, here are what I've tried so far:

SELECT
    com.name,
    p.name,
    COUNT(*)
FROM `company_person` 

INNER JOIN companies com 
ON com.id=company_id

INNER JOIN people p
ON p.id = person_id

WHERE position_id IN(1, 2) # 1=Owner, 2=CEO
GROUP BY company_id, person_id
HAVING COUNT(*) > 1;

Which Gives me a result which I belive is not accurate this query:

| com       | person        | COUNT(*) 
| ---       | ------        | -------- 
| Apple     | Steve Jobs    | 2 
| Microsoft | Bill Gates    | 2 
| Tesla     | Elon Mask     | 2 
| SpaceX    | Elon Mask     | 2

My question is, is this way the proper way?

Can you please help me out if you know another proper way which is correct and better than my way?

Rohit Gupta · Answer

From what I understand that you are wanting, there are a number of issues

Your primary table has multiple entries for the same company_id and person_id, so you have to do a distinct.
Count makes no sense at all, so I have dropped it.
IN will do an OR function, you want an AND. I have done two sub-queries for this aliasing the company_person to a different name to avoid confusion.

So here is one simple solution to illustrate the concept.

select distinct p1.person_id, p1.company_id, com.name company, p.name person
from company_person p1
inner join companies com 
on com.id=p1.company_id
inner join people p
on p.id = p1.person_id
where p1.position_id = 1
and exists (select p2.position_id 
              from company_person p2 
              where p2.position_id = 2)

I have left the ids in there as it helps to build up the query before you do the joins to fetch the names. You can remove them from the select.

Mysql Select query: Select employees that their position are both CEO and Owner

1 Answers