mysql request retrieve data combined with 2 tables

Question

I have three tables

I would like to request all persons, where the companyTypeID is for example '2'. How can I query that?

Answer 1

You could use the in operator:

SELECT *
FROM   persons
WHERE  CompanyId IN (SELECT CompanyId
                     FROM   company
                     WHERE  CompanyTypeId = 2)

Answer 2

Do an INNER JOIN (left or right joins are functionally similar, the only difference is which side of the equation is honoured). Nested queries / subqueries are extremely expensive if they become dependent in nature—even though that's not the scenario in your case—and I do not recommend using them for large tables.

SELECT t1.*
FROM Persons AS t1
LEFT JOIN Company AS t2 ON
    t2.companyTypeID = t1.CompanyID

To ensure that you are using an index for joining, you should create indexes on the companyTypeID and CompanyID columns of each table. Prepend EXPLAIN EXTENDED to the query above to verify that the indexes are indeed being used.

Answer 3

Perform an SQL join:

Joins are quicker than the subquery, in the other post:

SELECT Persons.firstname AS first name
  FROM Persons
  JOIN Company ON company.ID == Persons.CompanyID
 WHERE Company.companyTypeID == 2

Although you will need to select all he fields you want, using alias to simplify the names

Answer 4

You need to use SQL statement JOIN. It's all about mathematical sets!

A Graphic (and superficial) explanation about JOINs statement under mathematical sets approach:

https://www.google.com.br/imgres?imgurl=http%3A%2F%2Fi.imgur.com%2FhhRDO4d.png&imgrefurl=https%3A%2F%2Fwww.reddit.com%2Fr%2Fprogramming%2Fcomments%2F1xlqeu%2Fsql_joins_explained_xpost_rsql%2F&docid=q4Ank7XVw8j7DM&tbnid=f-L_7a3HkxW_3M%3A&w=1000&h=740&client=ubuntu&bih=878&biw=1745&ved=0ahUKEwj2oaer5evPAhUBFZAKHe4nAdAQMwgdKAEwAQ&iact=mrc&uact=8#h=740&w=1000

SQL for your problem(If I got this):

SELECT * FROM Persons p LEFT JOIN Company c ON c.ID = p.companyID
                        LEFT JOIN CompanyType ct ON ct.ID = c.companyTypeID
WHERE c.id = 2;

Why 'LEFT JOIN'? Checkout the the link about set approach explanation above!

The second 'LEFT JOIN' is just to bring the description of companyType table.

The "*" in statement is didatic! You must not use this in production for the good of performance. Therefore, you must to replace the '*' with all the fields you need. For example, "CONCAT(p.firstname,CONCAT(" ",p.lastname)) as PersonName, c.name CompanyName,..." or something like that. I suppose you're using MySQL

Hope I've helped!