I am attempting to generate a list (from four tables) of products, classid, optionid, 'images'. Each product can have many ClassIDs (one to many), each ClassID can have many OptionID, and each OptionID can have many many images. Finally for the images table (xcart_images_D) I would like to count() how many images each productID has. How can I effectively query all of this information without using multiple statements?

Here's the basic information for each of the tables

xcart_products
    + productid*
    + product
xcart_classes - 
    + classid*
    + productid (xcart_products.productid)
xcart_class_options -
    + optionid*
    + classid (xcart_classes.classid)
    + option_name
xcart_images_D - 
    + imageid*
    + optionid (xcart_class_options.optionid)
+ id (xcart_products.productid)

 * primary/foreign key

I have the three table join working, but I can't get the xcart_images_D table to work nor do I understand how I would accomplish the count. Can anybody point me in the right direction?

For what it's worth here is my three table join

SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name
FROM xcart_products xp
JOIN xcart_classes xc ON xp.productid = xc.productid and xc.class = 'Color'
JOIN xcart_class_options xco ON xc.classid = xco.classid
WHERE xc.class = 'Color'
ORDER by xp.productid DESC

When I try and add the following lines the number of results are reduced by about half.

 JOIN xcart_images_D xi ON xi.optionid = xco.optionid
 GROUP BY xp.productid

• mysql
• join

SELECT xp.productid, count(xi.imageid)
FROM xcart_products xp 
INNER JOIN xcart_classes xc ON xp.productid = xc.productid AND xc.class = 'Color'
INNER JOIN xcart_class_options xco ON xc.classid = xco.classid 
INNER JOIN xcart_images_D xi ON xi.optionid = xco.optionid
GROUP BY xp.productid
ORDER by xp.productid DESC