Re: how do i retrieve distinct rows using IN
On 15 Jan 2003, at 20:13, Ken Easson wrote:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
It's possible that the query you want is
SELECT DISTINCT catNum, price FROM sys_bld_foo
WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
or
SELECT catNum, MAX(price) FROM sys_bld_foo
WHERE catNum IN ('item1', 'partA', 'partB', 'partB')
GROUP BY catNum;
Maybe MAX() in the second one should be MIN() or AVG(), or maybe it
doesn't matter. I still don't understand why your table repeats the
price each time a catNum appears (rather than having a separate table
of parts, or whatever the catNum represents, that includes the price
and other data for each), nor why you say that normalizing would
require 20 new tables. But if you don't want to normalize, good
luck!
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how do i retrieve distinct rows using IN
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB',
'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum{partB} =
2, and then for each item returned, check the hash and multiple the price by it's
results. This seems extremely slow if my problem can be solved in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do i retrieve distinct rows using IN
SELECT SUM( price ) from sys_bld_foo where catNum IN ('item1', 'partA',
'partB') group by catNum;
Ken Easson wrote:
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum{partB} = 2, and then for each item returned, check the hash and multiple the price by it's results. This seems extremely slow if my problem can be solved in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do i retrieve distinct rows using IN
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
is a valid SQL statement from all i can see
my return value ignores the second partB - and my price is short.
i dont understand what you mean by 'short'
you comment that if you remove the DISTINCE that it 'doubles up' the partA and
partB data... yes correct.. that is how your data in the database is.
multiple rows for 150.50 price
i think you have your items and parts (of items) in the same table and that
generally is a bad idea.. from your data i would say you have 7 items in your
table... OR 7 parts but i think you are trying to say you have 2 items,
with parts for them maybe??
What is the problem, the business problem you are trying to solve? or what do
you need from your data? i think you are just querieng for the wrong
information .. but since i dont know what you are trying to do, i cant say
more right now.
Id need more information..
Brian Lindner
-- Original Message -
Subject: how do i retrieve distinct rows using IN
Date: Wed, 15 Jan 2003 15:12:37 -0400
From: Ken Easson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum
{partB} = 2, and then for each item returned, check the hash and multiple the
price by it's results. This seems extremely slow if my problem can be solved
in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do i retrieve distinct rows using IN
hello,
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
problem --|--|
partB is duplicated in the IN list. i need IN to find a DISTINCT price for EACH
element in the list.
Gerald's solution i think gives me the same incorrect sum that i get when i sum the
results outside of mysql, but i do like the idea, if there is an sql that will
generate the correct result set, i will use the SUM - save me some of code.
my database includes the general category a part falls into - monitor, cpu,
motherboard, hard drive etc.
and then the catalog code (catNum); amd 2.8Gh processor, intel 3.0Ghz processor etc.
normalizing the category column results in about 20 tables, each with about 6 items
each, which i think is a classic example of over-normalization, also, virtually every
call to the database will retrieve 1 or more parts from each category, again
normalization would create a terrific slowdown in the search, and overcomplicate my
database.
my primary key is a combination of both the component, and the category number. This
is to allow each component to have none as a valid catNum.
however what i hadn't taken into consideration is that some computers have 2 identical
hard drives. which is were my particular problem lies, there are two components
'primary hard drive', and 'secondary hard drive', with virtually the same list of
catNum's, and if a user only selects one hard drive, the catNum IN (hardDrive1) return
the price for the hard drive in primary hard drive, and secondary hard drive, or
effectively doubling the price of that component.
To solve this problem i used the DISTINCT keyword, however now when we build a system
with two identical hard drives, (hardDrive 1, hardDrive 1) the system see this as a
duplicate record and ignores the second hard drive, again the incorrect sum of the
price.
Yes i know it's ugly from a clean database structure, but having only one set of hard
drives, but doubling them up in the actually program again makes everything else much
more complicated.
What i am looking for is something like this:
foreach (hardDrive1, hardDrive1, monitorA, CPUA, VideoCardD)
SELECT price FROM sys_comp WHERE catNum = $_
}
hope that makes it clearer what i am trying to accomplish.
At 09:56 PM 1/15/2003 +, you wrote:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
is a valid SQL statement from all i can see
my return value ignores the second partB - and my price is short.
i dont understand what you mean by 'short'
you comment that if you remove the DISTINCE that it 'doubles up' the partA and
partB data... yes correct.. that is how your data in the database is.
multiple rows for 150.50 price
i think you have your items and parts (of items) in the same table and that
generally is a bad idea.. from your data i would say you have 7 items in your
table... OR 7 parts but i think you are trying to say you have 2 items,
with parts for them maybe??
What is the problem, the business problem you are trying to solve? or what do
you need from your data? i think you are just querieng for the wrong
information .. but since i dont know what you are trying to do, i cant say
more right now.
Id need more information..
Brian Lindner
-- Original Message -
Subject: how do i retrieve distinct rows using IN
Date: Wed, 15 Jan 2003 15:12:37 -0400
From: Ken Easson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum
{partB} = 2, and then for each item returned, check the hash and multiple the
price by it's results. This seems extremely slow if my problem can be solved
in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before
