满足条件的PHP查询必须存在于第二个表中

Question

I have two tables.

First has the following:

id | Company       | Field  | Country
-------------------------------------
1  | Widgets Inc   | Parts  | USA   
2  | Moo Corp      | Toys   | GBR   
3  | Dingles S.A   | Music  | GER   
4  | Deutsh Import | Import | AUT   
5  | Clean Inc     | Clean  | USA   

and second table

id | Employee     | Company_id | Country
----------------------------------------
1  | Paul Allen   | 2          | USA   
2  | Andrew Cur   | 2          | GBR   
3  | Paul Hanz    | 4          | GER   
4  | Angela Dow   | 1          | AUT   
5  | Dana Loconto | 4          | USA   

I want to filter (mysql query on php) the first table with ONLY the companies that actually have an employee on the second table. The ones with no employees should be ignored.

Thank you for your advice

EDIT: Using INNER JOIN seems to fix this, but then I have a different problem. If I use INNER JOIN as suggested below, it gives me one result for EACH time the occurence happens on the join table.

For example: Using my table below, it will return the company id 2 twice, and id 4 twice. To better explain, I'm using this to generate a list of the companies that actually have employees, but I don't need it to repeat itself everytime it finds a new employee of the same company. Not sure if I'm making myself clear.

SELECT C.id, C.field, C.country
FROM first_table as C
INNER JOIN second_table as E ON E.company_id = C.id

This returns the following

Moo Corp       | Toys   | USA
Moo Corp       | Toys   | USA
Deutsch Import | Import | AUT
Widgets Inc    | Parts  | USA
Deutsch Import | Import | AUT

And what I was expecting is only

Moo Corp
Deutsch Import
Widget Inc

Any extra help?

Answer 1

Try an inner join :

SELECT C.id, C.field, C.country
FROM first_table as C
INNER JOIN second_table as E ON E.company_id = C.id AND E.employee is not null

Or as suggested in the comment, if the second_table data about employee only exist if there is a company :

SELECT C.id, C.field, C.country
FROM first_table as C
INNER JOIN second_table as E ON E.company_id = C.id

It works like this :

• You select the element you want (here only element from table_one)
• You make your inner join based on the common field (here company_id from second_table and id from first_table)
• You add your condition on the inner_join, here E.employee is not null, adapt according to how it looks when no employee (maybe empty string, maybe 0, I don't know)
• The inner join will make you return only element that respect this condition

EDIT :

To only get one result by company, add this at the end :

GROUP BY C.company

But you need to add C.company to your SELECT before