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
