I am using MySQL 3.23.39 with Perl and DBI.
I have several tables that I need to return results from, and also a table
that I want to perform multiple "count" statements on. Ideally, I would
like all of the results to be returned as one set.
The tables are as follows:
products (id, model, sound_id)
sound_formats (id, name)
images (id, product_id, image_format_id)
The images table contains many images that are related to one product by
product_id.
There are different types of image for each product, and they are
categorised by image_format_id.
Ideally, I would like to have a set of results returned that contains the
following:
product.id, product.model, sound_formats.name, count of images where
image_format_id=1, count of images where image_format_id=2, count of images
where image_format_id=3
I can return the parts that I need, but not together:
The product.id, product.model and sound_formats.name is returned with:
select products.id, products.model, sound_formats.name
from products, sound_formats
where sound_formats.id = products.sound_id
and I can return the individual counts for a given product by doing:
select count(*) from images where product_id=30 and image_format_id=1;
select count(*) from images where product_id=30 and image_format_id=2;
select count(*) from images where product_id=30 and image_format_id=3;
Could anyone tell me if it would be possible to tag these results onto those
returned by the select products.id, products.model.. statement? I get the
feeling that it may be able to be done with some sort of nested statement,
but am not too sure about how to go about this.
I know if could be done by having the count of images stored as a permanent
value in the products table, but I wanted to avoid that if possible.
If the above is not possible, I shall open up a second connection to the
database and perform the individual queries while looping through the
results of the first query.
Thank you in advance for your time and help.
Kind regards,
Roland
