Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2003
    Posts
    23

    Exclamation Unanswered: [multivalued DBMS] How can I make this SELECT?

    Hi everybody!

    This is my first post on this board, so excuse me for my poor english and if I'm not respecting any rules here.

    I have a problem to make a query that seems simple tough.

    There is only one table:

    Products:
    id (int) 1:1 unique and indexed
    name (string) 1:1
    manufacturers (int) 1:n multivalued

    I want to select the name of all the products that have the most manufacturers.

    I think the query should be like this:

    SELECT P.name FROM Products AS P
    WHERE count(P.manufacturers) = MAX(count(P.manufacturers))

    But I don't have any multivalued DBMS to test this and I just need the syntax to make an algorithm that will translate it to a non multivalued DBMS

    Thanks for helping me.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there might be something in sql 2003 but i doubt it

    the problem is that your one-to-many multivalues list of id numbers fails first normal form

    sql is not designed to accommodate such failings easily

    redesign your database, and then it's easy to get what you want

    rather than multiple id values in one field, you want multiple rows in a related table


    rudy

  3. #3
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    7
    I think this is what you are after (you need to do a subquery to get the max):
    SELECT P.name FROM Products AS P
    WHERE P.manufacturers = (SELECT MAX(manufacturers) FROM Products)

  4. #4
    Join Date
    Apr 2003
    Posts
    23
    Thanks for responding

    I agree that this design for DB is not a good way according usual way, so I'm gonna give a little more explainations:

    I'm working on a conceptual DBMS, it should be a perfect DBMS that allows everything that should be possible in SQL, TSQL and OSQL.

    This DBMS doens't exist, of course, but is used to used 4 differents geographical Databases that have their own limitations. This DBMS is thus an interface for the existing DBMS an some others that we sould add in the future.

    It makes the scientis able to imagine the DB they need without any limitation. Then the programms will create a template for a classical relational DB and will translate all the query in it.

    So, that's why I need to know how this kind of query should be done, in order to make something that wil translate it.

    Hope i've been clear enough,
    regards.

  5. #5
    Join Date
    Apr 2003
    Posts
    23
    Originally posted by BruceG
    I think this is what you are after (you need to do a subquery to get the max):
    SELECT P.name FROM Products AS P
    WHERE P.manufacturers = (SELECT MAX(manufacturers) FROM Products)
    thanks, but this isn't. I don't need to know the highest id of manufacturers, but the number of manufacturers in this field.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > It makes the scientist able to imagine the DB
    > they need without any limitation.

    use a data model diagram to communicate your database design

    a good tool like ERwin can then generate the DDL for any database system you choose

    rudy

  7. #7
    Join Date
    Apr 2003
    Posts
    23
    If I could, I would...

    But I'm not the author of this project, just a little student doing his end studies work...

  8. #8
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    7
    Sorry, I misinterpreted the scenario. Can you supply a sample set of values, along with the results you would expect?

  9. #9
    Join Date
    Apr 2003
    Posts
    23
    Sure, here is an example:

    Products:
    [1][puppet][1, 3, 5]
    [2][ballon][2]
    [3][teddy bear][1, 2, 4]
    [4][puzzle][3, 6]

    (separator are here just for the example)

    The result should be
    [puppet]
    [teddy bear]

    because all of them have 3 manufacturers and that it's the maximum found in the table.

    Hope this help.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by le mogwai
    Sure, here is an example:

    Products:
    [1][puppet][1, 3, 5]
    [2][ballon][2]
    [3][teddy bear][1, 2, 4]
    [4][puzzle][3, 6]

    (separator are here just for the example)

    The result should be
    [puppet]
    [teddy bear]

    because all of them have 3 manufacturers and that it's the maximum found in the table.

    Hope this help.
    OK, in a real database you need 3 tables:

    create table products
    ( product_id number primary key
    , product_name varchar2(30)
    );

    create table manufacturers
    ( manuf_id number primary key
    , manuf_name varchar2(30)
    );

    create table product_manufacturers
    ( product_id number references products
    , manuf_id number references manufacturers
    , primary key (product_id,manuf_id)
    );

    These are populated with your sample data like this:

    select * from products;

    PRODUCT_ID PRODUCT_NAME
    ---------- ------------------------------
    1 puppet
    2 balloon
    3 teddy bear
    4 puzzle

    select * from manufacturers;

    MANUF_ID MANUF_NAME
    ---------- ------------------------------
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6

    select * from product_manufacturers;

    PRODUCT_ID MANUF_ID
    ---------- ----------
    1 1
    1 3
    1 5
    2 2
    3 1
    3 2
    3 4
    4 3
    4 6

    The following query, which works in Oracle at least, gets the result you seek:

    select p.product_name
    from products p, product_manufacturers pm
    where p.product_id = pm.product_id
    group by p.product_name
    having count(*) =
    ( select max(cnt) from
    ( select product_id, count(*) cnt
    from product_manufacturers
    group by product_id
    )
    );

    PRODUCT_NAME
    ------------------------------
    puppet
    teddy bear

  11. #11
    Join Date
    Apr 2003
    Posts
    23
    thanks for your response, but i know that already...

    I know how this should be in a "real" DB, but what i need is how it could be in this kind of DB

    I know Oracle can support this kind of table by using Varray or nested tables since version 8, but I don't have this DB to make my test.

    It seems that you have this one, it would help me a lot if you could make this little test.

    Thanks a lot.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by le mogwai
    thanks for your response, but i know that already...

    I know how this should be in a "real" DB, but what i need is how it could be in this kind of DB

    I know Oracle can support this kind of table by using Varray or nested tables since version 8, but I don't have this DB to make my test.

    It seems that you have this one, it would help me a lot if you could make this little test.

    Thanks a lot.
    Oh, if you insist. I LOATHE using nested tables, I mean - what is the ADVANTAGE? I can see the disadvantages all right...

    Code:
    SQL> create type manuf_t is table of number;
      2  /
    
    Type created.
    
    SQL> create table products
      2  ( product_id number primary key
      3  , product_name varchar2(30)
      4  , manufacturers manuf_t
      5  ) nested table manufacturers store as product_manufacturers;
    
    Table created.
    
    SQL> insert into products values (1,'puppet',manuf_t(1,3,5));
    
    1 row created.
    
    SQL> insert into products values (2,'balloon',manuf_t(2));
    
    1 row created.
    
    SQL> insert into products values (3,'teddy bear',manuf_t(1,2,4));
    
    1 row created.
    
    SQL> insert into products values (4,'puzzle',manuf_t(3,6));
    
    1 row created.
    
    SQL> select product_name
      2  from   products p, table(p.manufacturers) pm
      3  group by p.product_name
      4  having count(*) =
      5  ( select max(cnt) from
      6    ( select count(*) cnt
      7      from products p, table(p.manufacturers) pm
      8      group by p.product_id
      9    )
     10* );
    
    PRODUCT_NAME
    ------------------------------
    puppet
    teddy bear
    Using VARRAY the identical code above works, just change the TYPE definition to:

    SQL> create type manuf_t as varray(10) of number;

    This limits you to a maximum number of manufacturers per product (e.g. 10 in my example).

  13. #13
    Join Date
    Apr 2003
    Location
    NJ
    Posts
    7
    This will be tough in straight SQL because the comma-delimited string of manufacturers would have to be parsed to count how many commas there are in the string; I don't know of a SQL function that can do that.

    Assuming the manufacturer IDs are all roughly the same length, you could do something like:
    SELECT P.name FROM Products AS P
    WHERE LENGTH(P.manufacturers) = (SELECT MAX(LENGTH(manufacturers)) FROM Products)

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bruce, tony, you guys are way too nice

    the solution is to read the table sequentially with PHP

    i mean, isn't that where the concept of a comma-delimited list being good design seems to be coming from nowadays?


  15. #15
    Join Date
    Apr 2003
    Posts
    23
    Originally posted by andrewst
    Oh, if you insist. I LOATHE using nested tables, I mean - what is the ADVANTAGE? I can see the disadvantages all right...

    Code:
    SQL> create type manuf_t is table of number;
      2  /
    
    Type created.
    
    SQL> create table products
      2  ( product_id number primary key
      3  , product_name varchar2(30)
      4  , manufacturers manuf_t
      5  ) nested table manufacturers store as product_manufacturers;
    
    Table created.
    
    SQL> insert into products values (1,'puppet',manuf_t(1,3,5));
    
    1 row created.
    
    SQL> insert into products values (2,'balloon',manuf_t(2));
    
    1 row created.
    
    SQL> insert into products values (3,'teddy bear',manuf_t(1,2,4));
    
    1 row created.
    
    SQL> insert into products values (4,'puzzle',manuf_t(3,6));
    
    1 row created.
    
    SQL> select product_name
      2  from   products p, table(p.manufacturers) pm
      3  group by p.product_name
      4  having count(*) =
      5  ( select max(cnt) from
      6    ( select count(*) cnt
      7      from products p, table(p.manufacturers) pm
      8      group by p.product_id
      9    )
     10* );
    
    PRODUCT_NAME
    ------------------------------
    puppet
    teddy bear
    Using VARRAY the identical code above works, just change the TYPE definition to:

    SQL> create type manuf_t as varray(10) of number;

    This limits you to a maximum number of manufacturers per product (e.g. 10 in my example).
    GREAT! You're my god!

    In fact, I'm in accord with you. I don't find any advantage of this use of DB, but I have no choice for doing it.

    Well, one more time, thanks to all of you for your help and your patience.

Posting Permissions

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