درود

برای join سه تا جدول میتونید از syntaxt زیر استفاده کنید

SELECT discout.id, category_discounts.discount_id FROM discount 
join product_discounts ON discout.id = product_discounts.discount_id
join category_discounts ON discout.id = category_discounts.discount_id

لطفا یه ساختار درست از جداولتون بدید تا من بدونم کوئری باید به چه شکل باشه، مثل

product

productID

name

price

prod_cat

productID

categoryID

category

categoryID

name

بله همینطور هستش 
به این شکل به نتیجه رسیدم

public function join_product__with_productDiscounts_discounts($id)
    {
        $exists_productDiscount_by_product_id = (new Product_discount())->read_productDiscount_by_product_id($id);
        if ($exists_productDiscount_by_product_id) {
            return $this->connection->query("
                SELECT
                products.*,
                discounts.title AS discounts_title,
                discounts.percent AS discounts_percent,
                discounts.id AS discounts_id,
                discounts.status AS discounts_status
                FROM products
                INNER JOIN product_discounts
                ON products.id = product_discounts.product_id
                INNER JOIN discounts
                ON product_discounts.product_id = products.id
                AND products.id =$id
                ")->fetchAll();
        }
        return false;
    }