3 Different tables have same Foreign Key, How to select

Question

Supposed I have 3 different tables which have identical Foreign Key like below:-

1. Table One product_unit

   | id  |  product_id  |  weight |  status_id  |
   |:----|--------------|---------|------------:|
   |  1  |      4       |   300   |      1      |
   |  2  |      5       |   120   |      2      |
   

2. Table Two product_package

   | id  |  product_id  |  weight |  status_id  |
   |:----|--------------|---------|------------:|
   |  1  |      4       |   1.2   |      1      |
   |  2  |      5       |   480   |      1      |
   

3. Table Three product_carton

   | id  |  product_id  |  weight |  status_id  |
   |:----|--------------|---------|------------:|
   |  1  |      4       |  10.2   |      1      |
   |  2  |      5       |   4.8   |      2      |
   

Where table product and status table as shown below:-

a. product table

    | id  |     name      |
    |:----|--------------:|
    |  4  |  Choco Cake   |
    |  5  | Hazelnut Bun  |

b. status table

    | id  |  description  |
    |:----|--------------:|
    |  1  |   Available   |
    |  2  |  Unavailable  |

How can I get all status from those 3 tables (product_unit, product_package & product_carton) with 1 query ?

So far, I'm able to do this:-

$product_info = Product::find()
->select([
      'product.name AS productName',
      'product_unit.weight AS weightUnit',
      'product_package.weight AS weightPackage',
      'product_carton.weight AS weightCarton', 
      'status.description AS statusDesc'])
->leftJoin('product_unit', 'product.id = product_unit.product_id')
->leftJoin('product_package', 'product.id = product_package.product_id')
->leftJoin('product_carton', 'product.id = product_carton.product_id')
->leftJoin('status', 'status.id = product_unit.status_id')
->asArray()
->all();

With above query I can get 'statusDesc' from product_unit table ONLY, since I 'leftJoin()' status table with product_unit table.

How can I JOIN the other 2 tables (product_package & product_carton) and get the statusDesc from them also? Is there any way I can do this with 1 Query?

Answer 1

$product_info = Product::find()
->select([
      'product.name AS productName',
      'product_unit.weight AS weightUnit',
      'product_package.weight AS weightPackage',
      'product_carton.weight AS weightCarton', 
      's1.description AS status_unit_Desc'])
      's2.description AS status_package_Desc'])
      's3.description AS status_carton_Desc'])
->leftJoin('product_unit', 'product.id = product_unit.product_id')
->leftJoin('product_package', 'product.id = product_package.product_id')
->leftJoin('product_carton', 'product.id = product_carton.product_id')
->leftJoin('status AS s1', 's1.id = product_unit.status_id')
->leftJoin('status AS s2', 's2.id = product_package.status_id')
->leftJoin('status AS s3', 's3.id = product_carton.status_id')
->asArray()
->all();

PS. Maybe innerJoin will be more suitable then leftJoin?