|
|
Posted by mazzarin on 10/02/84 11:50
I am trying to generate some datasets with some queries...
With a given series information, it should return PART_NOs that has STD
= 1 and a unique price at that particular 'START', and keeping the
'TYPE' in consideration...
DB examples below:
Main DB
ID PART_NO SERIES STD
1 A-1 A 1
2 A-2 A 1
3 A-3 A 1
4 D-1 D 1
5 D-2 D 0
Price DB
ID PART_ID TYPE START PRICE
50 1 X 1000 50
51 1 X 10000 40
52 1 Y 1000 60
53 1 Y 10000 50
54 2 X 1000 50
55 2 X 10000 40
56 2 Y 1000 60
57 2 Y 10000 50
58 2 X 1000 90
etc.
main.ID and Price.PART_ID are paired together.
So in an example case, lets say I am querying for SERIES A, with TYPE
X. A table should be outputted something like
PART_NO
A-1 1000 50
A-1 10000 40
A-3 1000 90
Note how it skipped printing A2 because the price is the same as A1.
I'm really looking for the SQL code here... I can't get it to filter on
distinct price.
SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
FROM MAIN, PRICING
WHERE (MAIN.SERIES LIKE 'A')
AND (MAIN.STD = '1')
AND (PRICING.PRICE != '')
AND (PRICING.TYPE = 'X')
AND (MAIN.ID = PRICING.PART_ID)
I've been trying to use GROUP BY and HAVING to get what I need but it
doesn't seem to fit the bill. I guess I'm not terribly clear on how I
can use the SQL DISTINCT command...? If I try and use it in my WHERE
statement it gives me syntax errors, from what I understand you can
only have distinct in the select statement? I'm not sure how to
integrate that into the query to suit my needs.
Thanks for any help.
Navigation:
[Reply to this message]
|