Table: user

Table: Company

need a MYSQL query which should fetch company record only if it has one or more users, when passed a company id. I will have other where conditions on company table.

Can this be achieved by joins?

example 1: query(comp1) result: coke (atleast one user exists)

example 2: query(comp2) result: no records (Since no user exists who belong to company comp2)

CodePudding user response：

What you're asking for is called a semi-join. This returns one row from company if there are one or more matching rows in user.

If you use a regular join:

SELECT c.* FROM company c JOIN user u ON u.compid = c.id;

This does return the row from company, but you might not like that it returns one row per user. I.e. rows in the result are multiplied by the number of matches.

There are several possible fixes for this, to reduce the results to one row per company.

SELECT DISTINCT c.* FROM company c JOIN user u ON u.compid = c.id;

SELECT c.* FROM company c JOIN (SELECT DISTINCT compid FROM user) u ON u.compid = c.id;

SELECT * FROM company c WHERE c.id IN (SELECT compid FROM user);

SELECT * FROM company c WHERE EXISTS (SELECT * FROM user WHERE compid = c.id);

Which one is best for your app depends on many factors, such as the sizes of the tables, the other conditions in the query, etc... I'll leave it to you to evaluate them given your specific needs.

CodePudding user response：

Maybe this is too simple, but using an inner join will meet this criteria for you

select *
from user u
,company c
where u.compId = c.id