Assistance building a query...

[mazzarin@gmail.com]

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.

[mazzarin@gmail.com]

Re: Assistance building a query...

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:[color=blue]
> 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.[/color]

[mazzarin@gmail.com]

Re: Assistance building a query...

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:[color=blue]
> 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:[color=green]
> > 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.[/color][/color]

[Erland Sommarskog]

Re: Assistance building a query...

(mazzarin@gmail .com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[mazzarin@gmail.com]

Re: Assistance building a query...

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:[color=blue]
> (mazzarin@gmail .com) writes:[color=green]
> > 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.[/color]
>
> 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@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]