|
|
Posted by mazzarin on 10/02/10 11:50
Actually never mind, it doesn't do exactly what I want it to do... All
the prices are still duplicated, ideally I should only have at most 3
results being returned (according to the actual data being fed in)
I am beyond confused heh
I think I might have to do the filtering outside of SQL
mazzarin@gmail.com wrote:
> A bit of clarification on my problem
>
> If I just do a straight SQL distinct on my select statement, it does
> what I want when you get down to it, but it completely destroys the
> organization of the table. The part numbers were entered in a certain
> manner and I do not believe they can be reorganized through any typical
> sort. For example A-105A is higher then A-400B, but if you sorted in
> Excel (for example) it would put 105 below 400
>
>
> mazzarin@gmail.com wrote:
> > 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]
|