Sql to fetch records only if related other table records exist

Table: user

id	compId
1	comp1
2	comp1

Table: Company

id	name
comp1	coke
comp2	pepsi

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)

Answer

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.