-1

I want to get rows from child and from it's child table in MySQL.

Supposed I have the following tables with their columns:

Table product

id 
name 
created 

Table inventory

id
tableA_id 
created 

Table expiry

id 
tableB_id 

Relationships:

  • table A is the parent table
  • table B is child of table A
  • table C is child of table B

  • tableA_id is foreign key to table A

  • tableB_id is foreign key to table B

Basically, I just want to get rows from

  • table B where created from table A is 30 days older
  • table C where id not exist in table B

Is this possible? Any suggestion is appreciated.

  • Thanks for editing @Ezlo. I'm new here so not yet aware with formatting for questions. – ash_01 Jul 16 '18 at 15:11
  • It would be more precise to provide SHOW CREATE TABLEs. – Rick James Jul 16 '18 at 19:37
0

Assuming that product is table A, inventory is table B and expiry is table C (i don't know why you generalized them), these would be the queries you can use for your 2 requirements.

  • table B where created from table A is 30 days older

    SELECT
        I.*
    FROM
        product P
        INNER JOIN inventory I ON P.id = I.tableA_id
    WHERE
        DATEDIFF(I.created, P.created) > 30 -- Assuming that product is created before inventory)
    
  • table C where id not exist in table B

    SELECT
        P.*
    FROM
        expiry P
    WHERE
        NOT EXISTS (SELECT 'not in inventory' FROM inventory I WHERE I.id = P.tableB_id)
    

I suggest changing the foreign key's column names to their actual table (if you actually have them that way). tableA_id to productID and tableB_id to inventoryID.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.