Assistance building a query...

[mazzarin]

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]

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
ma******@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.

[mazzarin]

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

ma******@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
ma******@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.

[Erland Sommarskog]

(ma******@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, es****@sommarskog.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

[mazzarin]

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:

(ma******@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, es****@sommarskog.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