CODEWITHSUNDEEP
mysqlsubquery
michele
michele

Reputation: 26598

subqueries mysql help

I have 3 tables in a MySql Database:

  1. User;
  2. Company that has a date field called data;
  3. Object that has idUser and idCompany.

I have to retrieve Companies that has got a specific date in date field of Company, and that meet the following rule: There should be no records in Object table that has idP=xx and idI=(an id of a company that has got the specific value date yy-mm-dd)

How can I write this query?

Upvotes: 0

Views: 55

Answers (2)

nobody
nobody

Reputation: 10635

SELECT * FROM Company
  LEFT JOIN Object ON( Object.idP = xx OR Object.idl = Company.id )
  WHERE Company.date = somedate AND Object.idP IS NULL

Upvotes: 1

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

SELECT * FROM Company WHERE data = '2011-08-09' AND
Company.id NOT IN
(SELECT Object.idI FROM Object WHERE Object.idI = Company.id AND Object.idP = 'XX')

But I have to say that your columns and tables naming need to be re-done. It could be a lot more meaningful.

You have to created indexes for Object.idI and Object.idP to make it run quicker.

Upvotes: 1

Related Questions