Join on 3 tables MySQL

Question

I have 3 tables in MySQL 5.5 :

product

id_product int primary
product_name varchar
creation_id int (index)

product_life

id_product_life int primary
product_event_name varchar
product_id int (index)
creation_id int (index)

date_created

id_creation int primary
date_creation datetime

"creation_id" from product and product_life tables are foreign keys (without constraint) for primary key id_creation of date_created table
"product_id" from product_life table is foreign key (without constraint) for primary key id_product of product table

I need to retrieve the number of products created on a specific day (date_creation >= '2014-08-01 00:00:00' AND date_creation <= '2014-08-01 23:59:59') but these products must have no records in product_life table having a date_creation >= '2014-08-10 00:00:00'

I tried with an INNER JOIN on the 3 tables but it doesn't work. Thanks for your help.

You have to try left outer join with null clause: http://stackoverflow.com/questions/1519272/mysql-not-in-query — bksi, Aug 28 '14 at 23:03
Show what you tried. [Give query, data & DDL.](http://stackoverflow.com/help/mcve) — philipxy, Aug 28 '14 at 23:11

score 0 · Answer 1 · edited Aug 28 '14 at 23:19

0

SELECT * FROM product 
INNER JOIN date_created 
        ON date_created.id_creation = product.id_creation 
WHERE (date_creation >= '2014-08-01 00:00:00' AND date_creation <= '2014-08-01 23:59:59') 
  AND (SELECT COUNT(*) 
       FROM product_life 
       INNER JOIN date_created 
               ON date_created.id_creation = product.id_creation 
       WHERE product_id = product.id_product 
         AND date_creation >= '2014-08-10 00:00:00') = 0

edited Aug 28 '14 at 23:19

Saechel

152
10

answered Aug 28 '14 at 23:08

quentinadam

3,058
2
27
42

Reasonable answer, although I think `NOT EXISTS (SELECT * ...)` is generally preferable over `(SELECT COUNT(*) ...) = 0`. – Barmar Aug 28 '14 at 23:31

Join on 3 tables MySQL

1 Answers1