2

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)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
dreambigcoder
  • 1,859
  • 4
  • 22
  • 32
  • I tried where exists (select * from user u where u.compId = id), but I have other where conditions as well which break the query. – dreambigcoder Sep 22 '21 at 13:55
  • Please add all clarification to your question by editing it. You haven't mentioned any other conditions in your question so far – Nico Haase Sep 22 '21 at 13:56
  • It is actually a very big query with many other table joins, I just want to know if this can be achieved by join condition instead of using where exists clause. – dreambigcoder Sep 22 '21 at 13:58

1 Answers1

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Excellent, I am refraining from using distinct before c.* second query SELECT c.* FROM company c JOIN (SELECT DISTINCT compid FROM user) u ON u.compid = c.id; does the job for me, thanks. – dreambigcoder Sep 22 '21 at 14:12