[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.

[CR6] Can I use "SELECT FROM WHERE"?

Hi, sorry for my english :-\

I'm working on Crystal Report 6.0
I have 2 tables that are not in join, but I need a field with a "where" condition.
Example:
TableA:
fldDescription

TableB:
fldCode
fldDesc

I have a formula field which should show TableB.fldDesc, using a code in TableA.fldDescription
I extract 2 char in TableA.fldDescription:
strMyField = Trim (Mid ({TableA.fldDescription},2 , 2));

Now I must show TableB.fldDesc where TableB.fldCode = strMyField.
How can I do this? I have no join between this tables, I tried to put in the formula field "Select TableB.fldDesc where TableB.fldCode = strMyField" but it doesn't work.

Could you help me, please?How are TableA and TableB related? Do you even need any data from TableA?

"Select TableB.fldDesc where TableB.fldCode = strMyField"

If this were VB, you would have to do it like this:

"Select TableB.fldDesc where TableB.fldCode = '" & strMyField & "'"

In other words, you would have to concatenate the variable into the string you're using for your select statement. Also, you would need to put the string in single quotes (as shown in Red) to tell your database that it's a string. (These examples are derived from VB 6 and SQL Server 7, so you may need to adjust them accordingly).

I don't use Crystal Reports for direct database access. I let VB do all the data gathering and I just pass the data itself to Crystal for displaying. I'm not sure what the correct syntax is for Database SQL Statement within Crystal Reports, but I hope that my examples might give you a starting point to figure out your problem.

Good Luck!

[6.5] Server language English but date format is Dutch

I have a Sql 6.5 database-server. When I open a query window and ask for getDate() it returns Tuesday, Sep 30 2003 12:00 AM.

So far so good.

However, when I connect through an ASP page on my webserver (through ODBC) and ask for getDate, I get a Dutch format: 30-09-2003.

My Sql Server is set to default (English US)
My Database user is set to English Us
My DB server's regional settings are set to US
My ODBC is set to use English as a language
My Webserver's regional settings are set to US (d/M/y)

I'm at wit's end. I have no clue as to why I am getting Dutch dates. I know I could just convert(varchar,...) but that is not an option right now.

Thanks in advance,

KristofDoes sound like a puzzle. What are the Web Clients settngs? Don;t know why that would matter unless you are runing code on the client side, but it's the only thing you don't have listed.|||The client side is as English (US) as it gets. Regional settings are English etc.

Glad to see you're puzzled as well ... Little comfort, but comfort :)|||As a test, try changing each of the servers' settings, one at a time, to a third setting (no idea off the top of my head what this would be) to see if and when a change occurs.

This could help give you a new angle (?!).|||Solved.

Apparently, you can have multiple regional settings on a server (NT4) and NT decides which one it picks. So I forced US to be the default system locale, booted the (live *glubs*) database server and all was well.

Thanks a lot for thinking with me!