I have three tables which defines a ternary relation.
-
tbl_product: product_id (primary key), name (varchar)
-
tbl_image: image_id (primary key), rate (int), path(varchar)
-
tbl_image_product: product_id, image_id
A product (tbl_product) can have multiple images(tbl_image), and an image(tbl_image) can below to several products(tbl_product). So the tbl_image_product table manages the ternary relation between them.
My problem comes when I want to retrieve the last six products (biggers product_id) with the image with low rate. The result of the query shows me the join with the small image_id.
The query I do is the next:
SELECT *
FROM tbl_product
t
LEFT OUTER JOIN tbl_image_to_product
tblImages_tblImages
ON (t
.product_id
=tblImages_tblImages
.product_id
)
LEFT OUTER JOIN tbl_image
tblImages
ON (tblImages
.image_id
=tblImages_tblImages
.image_id
)
GROUP BY t
.product_id
ORDER BY t
.product_id DESC, tblImages.rate ASC
LIMIT 6
Unfortunately I don’t get the low rate for the join I do. I would be pleased if someone could help me with the correct query or the correct CDbCriteria.
Thanks in advance:
Kike