FAQ
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

Search Discussions

  • Jonathan Leffler at Jan 25, 2002 at 5:17 am

    Roland Corbet wrote:

    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.
    With Informix, and probably some of the other commercial databases, you could
    write:

    select products.id, products.model, sound_formats.name,
    (select count(*) from images where images.product_id=products.id and
    image_format_id=1) AS count_format_1,
    (select count(*) from images where images.product_id=products.id and
    image_format_id=2) AS count_format_2,
    (select count(*) from images where images.product_id=products.id and
    image_format_id=3) AS count_format_3
    from products, sound_formats
    where sound_formats.id = products.sound_id

    I wouldn't want to claim it was efficient, but I believe it would work (barring
    syntax errors). I have doubts about whether MySQL can handle this.

    --
    Jonathan Leffler (jleffler@earthlink.net, jleffler@informix.com)
    Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
    #include <disclaimer.h>
  • Nikko Odiseos at Feb 12, 2002 at 7:07 pm
    I am a bit stuck. I have a sql 7 server on win2k and need to make queries
    to the db from a unix box. What is the driver that I need and also the unix
    driver manager for odbc. Thanks!
    Nikko
  • Jeff Urlwin at Feb 13, 2002 at 3:28 am
    Nikko,

    I think a solid choice would be unixODBC and EasySoft's ODBC bridge. Both
    can be obtained from www.easysoft.com, although a newer unixODBC version
    exists at www.unixodbc.org

    Jeff
    -----Original Message-----
    From: Nikko Odiseos
    Sent: Tuesday, February 12, 2002 1:46 PM
    To: Perl-DBI (E-mail)
    Subject: RE: Forming a set of results from multiple queries.


    I am a bit stuck. I have a sql 7 server on win2k and need to make queries
    to the db from a unix box. What is the driver that I need and
    also the unix
    driver manager for odbc. Thanks!
    Nikko

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJan 24, '02 at 12:26p
activeFeb 13, '02 at 3:28a
posts4
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase