If we look at the query mentioned in the comment on the question:

    FROM t_product
    JOIN t_order 
        ON t_order.customerid = 101 
    JOIN t_orderItem 
        ON t_product.productid = t_orderItem.productid 
    WHERE t_order.orderid=t_orderItem.orderid

Since we are now dealing only with INNER JOINs we can logically move predicates between the JOIN clause and the WHERE clause. We can also change the order of the JOINs. I would think of this as:

- An ORDER has ORDERITEMS

        FROM t_order  
        JOIN t_orderItem 
            ON t_order.orderid=t_orderItem.orderid

- An ORDERITEM references a PRODUCT

             t_orderItem 
        JOIN t_product      
            ON t_product.productid = t_orderItem.productid

- A CUSTOMER orders an ORDER 
  
             WHERE t_order.customerid = 101 
 
Gluing this together would be:

    SELECT t_product.productid
        ,  t_order.orderid
        ,  t_orderItem.itemid
    FROM t_order  
    JOIN t_orderItem 
        ON t_order.orderid=t_orderItem.orderid
    JOIN t_product      
        ON t_product.productid = t_orderItem.productid  
    WHERE t_order.customerid = 101 

If you want to have ORDERs that not yet have any ORDERITEMS you could use a LEFT JOIN between ORDER and ORDERITEMS (I don't think it will be any use in having an ORDERITEM without a PRODUCT)

    SELECT t_product.productid
        ,  t_order.orderid
        ,  t_orderItem.itemid
    FROM t_order  
    LEFT JOIN t_orderItem 
        ON t_order.orderid=t_orderItem.orderid
    JOIN t_product      
        ON t_product.productid = t_orderItem.productid  
    WHERE t_order.customerid = 101 

I would say that the intent of these queries is easier to understand than the query we started with.