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 +0000, 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 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
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
