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
