VBA/SQL Question

[mazzarin]

I am trying to generate some worksheets that utilizes VBA/ADODB to
query the MSSQL db for a given series.

With that 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.

..Open "SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE FROM MAIN,
PRICING WHERE (MAIN.SERIES LIKE " & givenseries & ") AND (MAIN.STD =
'1') AND (PRICING.PRICE != '') AND (PRICING.TYPE = 'X') AND (MAIN.ID =
PRICING.PART_ID)", dbConn, adOpenStatic

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...?

Thanks for any help.

 

[Charlie]

Without looking too deeply into what it is you are trying to do, I can tell
you I had trouble with "Select Distinct" and "Order By".

I was able to use "Select Distinct" but was not able to get it to work in
conjunction with "Order By". I'm not surprised "Group By" is also giving you
trouble. I don't know why.

e.g.
"Select distinct * from TableName where Whatever = 'something' Order By
somefield;" (didn't sort the data)

So I chose to just do a "Select Distinct" and do my own internal sort.

 

[mazzarin]

Yeah, perhaps I should restate my problem...

SQL distinct 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
duplicated 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]

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

Any help is appreciated