[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.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|||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 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.|||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.|||> 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|||If I could, I would...

But I'm not the author of this project, just a little student doing his end studies work...|||Sorry, I misinterpreted the scenario. Can you supply a sample set of values, along with the results you would expect?|||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.|||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|||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.|||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...:)

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).|||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)|||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?

;)|||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...:)

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! :D

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.|||Originally posted by r937
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?

;)
So true. And Nested tables are like comma-delimited lists on steroids! The trouble is, since they are one of the new OO features in Oracle, all rookie database designers think they should be using them rather than out-moded relational tables. Worse, they are encouraged/forced to use them by college courses that teach features rather than theory.