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
IDPART_NOSERIESSTD
1A-1A1
2A-2A1
3A-3A1
4D-1D1
5D-2D0
Price DB
IDPART_IDTYPESTARTPRICE
501X100050
511X1000040
521Y100060
531Y1000050
542X100050
552X1000040
562Y100060
572Y1000050
582X100090
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-1100050
A-11000040
A-3100090
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.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
> IDPART_NOSERIESSTD
> 1A-1A1
> 2A-2A1
> 3A-3A1
> 4D-1D1
> 5D-2D0
> Price DB
> IDPART_IDTYPESTARTPRICE
> 501X100050
> 511X1000040
> 521Y100060
> 531Y1000050
> 542X100050
> 552X1000040
> 562Y100060
> 572Y1000050
> 582X100090
> 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-1100050
> A-11000040
> A-3100090
> 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.|||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
> > IDPART_NOSERIESSTD
> > 1A-1A1
> > 2A-2A1
> > 3A-3A1
> > 4D-1D1
> > 5D-2D0
> > Price DB
> > IDPART_IDTYPESTARTPRICE
> > 501X100050
> > 511X1000040
> > 521Y100060
> > 531Y1000050
> > 542X100050
> > 552X1000040
> > 562Y100060
> > 572Y1000050
> > 582X100090
> > 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-1100050
> > A-11000040
> > A-3100090
> > 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.|||(mazzarin@.gmail.com) writes:
> 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.
But why does A-3 appear? Ir does not seem to appear in the Price DB
at all?
If there is an A-4 with the same values as A-1 would that be printed?
I'm sorry, but as you have presented the problem there are two many
unknowns. Furthermore, there is a standard recommendation that you include
in your post:
o CREATE TABLE statements for you table(s).
o INSERT statemetns with sample data.
o The desired result given the sample.
This makes it possible to easily copy and paste into Query Analyzer
to play around with the data and develop a tested solution.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I actually ended up doing multiple queries, passing info between VBA
and SQL. Its not as nice of a solution but it works. Thank you for your
advice though. I will keep it in mind next time!
Erland Sommarskog wrote:
> (mazzarin@.gmail.com) writes:
> > 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.
> But why does A-3 appear? Ir does not seem to appear in the Price DB
> at all?
> If there is an A-4 with the same values as A-1 would that be printed?
> I'm sorry, but as you have presented the problem there are two many
> unknowns. Furthermore, there is a standard recommendation that you include
> in your post:
> o CREATE TABLE statements for you table(s).
> o INSERT statemetns with sample data.
> o The desired result given the sample.
> This makes it possible to easily copy and paste into Query Analyzer
> to play around with the data and develop a tested solution.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment