Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: simple query problem !

    here is my one table:


    category | manufacturer
    -----------------------------
    baseball | adidas
    hockey | nike
    football | reebok
    soccer | nike


    i'm trying to query out all the distinct manufacturers for each category.
    i was trying to use this select statement, which isn't working... and i'm not sure if this is just because my query is wrong, or my version of mysql (4.0.15-standard) doesn't support sub-selects.
    here is the horrible query which is probably way off !

    select distinct category from table where exists (select distinct manufacturer from table)


    any insight would be greatly appreciated.
    tia !

  2. #2
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18
    Are you trying to get the manufacturers or categories? You say manufacturers, but then your query is written to "SELECT category".

    Can you be more specific about what your result set would look like?

  3. #3
    Join Date
    Oct 2003
    Posts
    13

    Red face doh !

    duh... sorry !

    i'm trying to list out all unique categories for each manufacturer... for example:


    nike
    ===
    hockey
    baseball
    soccer

    adidas
    ===
    hockey
    baseball
    football

  4. #4
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18
    Okay, here's a couple of solutions. First, if you want all the categories for a single manufacturer, you would...

    SELECT category FROM table WHERE manufacturer = '<manufacturer>'

    ...which would give you...

    <category1>
    <category2>
    <category3>

    ----------------------------------------------------

    I'm assuming you want something more complete...

    SELECT manufacturer, category FROM table ORDER BY manufacturer, category

    ...giving you...

    <manufacturer1> | <category1>
    <manufacturer1> | <category2>
    <manufacturer1> | <category3>
    <manufacturer2> | <category1>
    <manufacturer2> | <category3>

    ...assuming <manufacturer2> is not associated with <category2>, and so on and so on and so on...

    -----------------------------------------------------

    This is just how recordsets are presented. It would be up to you to write the code to parse them out to how you want them presented in your web page. Setting a local variable for the current manufacturer name and then watching for it to change to a new one as you loop through the recordset would be the standard way.

    It would be possible to return a two field recordset instead like so...

    <manufacturer1> | 'manufacturer'
    <category1> | 'category'
    <category2> | 'category'
    <category3> | 'category'
    <manufacturer2> | 'manufacturer'
    <category1> | 'category'
    <category3> | 'category'

    ...so all you have to know is whether the first field is a 'manufacturer' or 'category', but this is generally more trouble than it's worth.

    Hope this has helped.

  5. #5
    Join Date
    Oct 2003
    Posts
    13

    Thumbs up ..

    cipherlad, that made perfect sense. thank you very, very much for breaking that down into english

  6. #6
    Join Date
    Jun 2003
    Location
    Phoenix, AZ
    Posts
    8
    could always do:

    select distinct(category), manufacturer from table;

    I guess my real question is why would the same category be under a given manufacturer more than once? This would be the only reason to need to use distinct(). Unless of course the table you have shown is not the entire table?

    I would declare manufacturer/category as a unique key then there would be no duplicates.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •