Results 1 to 5 of 5

062215, 16:51 #1Registered User
 Join Date
 Jun 2015
 Posts
 4
Answered: Compare All Combinations in an Expression Between Two Different Tables
I am a mechanical engineer and I am building a database to keep track of information and help me design transmissions. I will break this problem down to simple terms to help eliminate confusion. I have two tables, with a large amount of records in each (4,0005,000).
Table #1
Code:EXAMPLE TABLE: DRIVE PULLEY ****************************************** Drive Pulley Part Number Diameter 100001 10.0 100002 12.0 100003 14.0 etc.... ... ******************************************
Table #2
Code:EXAMPLE TABLE: NEUTRAL PULLEY ****************************************** Neutral Pulley Part Number Diameter 200001 5.0 200002 7.0 200003 9.0 etc.... ... ******************************************
In another query I have an Theoretical Pulley Diameter Ratio (well say it is equal to 2.125 as an example).
The goal is to pick out the best combinations (one from each table above) that will get me closest to the Theoretical Pulley Diameter Ratio. that I need to choose these. The equation for the Actual Pulley Diameter ratio is
([DRIVE PULLEY].[DIAMETER] / [NEUTRAL PULLEY].[DIAMETER].
It's a very simple equation, but it has a tremendous amount of combinations.
An example of the calculations:
Actual Pulley Diameter:
Drive Pulley Part Number Diameter
100001 10.0
Neutral Pulley Part Number Diameter
200001 5.0
Actual Pulley Diameter Ratio = 10.0/5.0 =2.0
Code:EXAMPLE TABLE: DRIVE PULLEY ***************************************************************************************************** Drive Pulley Part Number Neutral Pulley Part Number Actual Pulley Diameter Ratio (sorted as close to 2.125) 100001 200001 2.00 (10/5 = 2.00) 100002 200002 1.71 (12/7 = 1.71) 100003 200003 1.70 (14/9 = 1.55) etc.... etc... etc. ********************************************************************************************************

healdem
Posted by"so lets assume you have a table for pulleys with all pulleys in them, and that the column names remain the same
'all' you need to do is revise the SQL slightly to refer to the same table using an alias
Code:SELECT D.PartNo, D.Diameter, N.PartNo, N.Diameter, D.Diameter / N.DIAMETER AS Ratio, 2.1 (D.Diameter / N.DIAMETER) as Variance FROM Pullies as D, Pullies as N where [D].[Diameter]/[N].[DIAMETER] between 2.1 .4 and 2.1 +.4 order by abs(2.1 [D].[Diameter]/[N].[DIAMETER])
Code:SELECT DrivePulley.PartNo, DrivePulley.Diameter, NeutralPulley.PartNo, NeutralPulley.Diameter, [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] AS RATIO FROM Pullies as DrivePulley, Pullies as NeutralPulley where [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] between 2.1 .4 and 2.1 +.4 order by abs(2.1 [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER])
for presentation
... D.Diameter / N.DIAMETER AS Ratio
for ease of writing I prefer to use short tabel aliases, hence why I used D for the drive pulley, and N for the neutral pulley
the where clause can be expanded to add other criteria as suyggested above, or if you find there are performnace issues (its taking a long time to find waht you want, then consider indexing say the diameter column AND addign additioanml criteria
such as
Code:...where [D].[Diameter]/[N].[DIAMETER] between 2.1 .4 and 2.1 +.4 AND [D].[Diameter] > [N].[DIAMETER]...

062215, 17:52 #2Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59as a SQL query thats going to be something like
Code:SELECT DrivePulley.PartNo, DrivePulley.Diameter, NeutralPulley.PartNo, NeutralPulley.Diameter, [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] AS RATIO FROM DrivePulley, NeutralPulley; WHERE [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] BETWEEN 2 AND 2.25
Code:DrivePulley.PartNo DrivePulley.Diameter NeutralPulley.PartNo NeutralPulley.Diameter RATIO 100001 10 200001 5 2 100003 14 200002 7 2
...without the between clause
Code:DrivePulley.PartNo DrivePulley.Diameter NeutralPulley.PartNo NeutralPulley.Diameter RATIO 100001 10 200001 5 2 100002 12 200001 5 2.4 100003 14 200001 5 2.8 100001 10 200002 7 1.42857142857142857142857 100002 12 200002 7 1.71428571428571428571429 100003 14 200002 7 2 100001 10 200003 9 1.11111111111111111111111 100002 12 200003 9 1.33333333333333333333333 100003 14 200003 9 1.55555555555555555555556
Code:SELECT DrivePulley.PartNo, DrivePulley.Diameter, NeutralPulley.PartNo, NeutralPulley.Diameter, [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] AS RATIO FROM DrivePulley, NeutralPulley; WHERE [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] BETWEEN 2 AND 2.25
$target is the desired ratio
$tolerance is the tolerance
...then the psuedo code is going to be something like
Code:SELECT DrivePulley.PartNo, DrivePulley.Diameter, NeutralPulley.PartNo, NeutralPulley.Diameter, [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] AS RATIO FROM DrivePulley, NeutralPulley; WHERE [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] BETWEEN $target  tolerance AND $target + tolerance order by ABS($target  [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER])
...the query would look like
Code:SELECT DrivePulley.PartNo, DrivePulley.Diameter, NeutralPulley.PartNo, NeutralPulley.Diameter, [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] AS RATIO FROM DrivePulley, NeutralPulley where [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] between 2.1 .4 and 2.1 +.4 order by abs(2.1 [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER])
Code:DrivePulley.PartNo DrivePulley.Diameter NeutralPulley.PartNo NeutralPulley.Diameter RATIO 100003 14 200002 7 2 100001 10 200001 5 2 100002 12 200001 5 2.4 100002 12 200002 7 1.71428571428571428571429
to parameterise a query see:
https://support.microsoft.com/enus/kb/200190
presuming this is some sort of design lookup, then you could do this in a form, write a query on the fly and make its as fiendishly complex as you require.
eg specify a belt width, teeth interval and desired ratio. you could have an upper and lower threshold (say .1 +.5), y get the picture. the above query has the refinement of ordering the results in closest to the $target ratioLast edited by healdem; 062315 at 05:30.
I'd rather be riding on the Tiger 800 or the Norton

062215, 17:54 #3Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59incidentally you don't need a separate table for drive and neutral pulleys
you could have a table for pulleys, and if required a column in that table that indicates if the pulley is a drive or neutral or both for that matterI'd rather be riding on the Tiger 800 or the Norton

062315, 09:35 #4Registered User
 Join Date
 Jun 2015
 Posts
 4
Code:EXAMPLE TABLE:PULLEY ****************************************** Pulley Part Number Diameter 100001 10.0 100002 12.0 100003 14.0 200001 5.0 200002 7.0 200003 9.0 etc.... ... ******************************************
Code:SELECT Pulley.PartNo, Pulley.Diameter, Pulley.PartNo, Pulley.Diameter, [PULLEY].[DIAMETER]/[PULLEY].[DIAMETER] AS RATIO FROM Pulley; WHERE [PULLEY].[DIAMETER]/[PULLEY].[DIAMETER] BETWEEN 2 AND 2.25
A pulley can be used as a drive or neutral pulley. There is no difference. How would I distinguish between the two combinations of the same field? Just as an example, would the code above work for something like this or would I have to approach it in a different way?

062315, 10:48 #5Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59so lets assume you have a table for pulleys with all pulleys in them, and that the column names remain the same
'all' you need to do is revise the SQL slightly to refer to the same table using an alias
Code:SELECT D.PartNo, D.Diameter, N.PartNo, N.Diameter, D.Diameter / N.DIAMETER AS Ratio, 2.1 (D.Diameter / N.DIAMETER) as Variance FROM Pullies as D, Pullies as N where [D].[Diameter]/[N].[DIAMETER] between 2.1 .4 and 2.1 +.4 order by abs(2.1 [D].[Diameter]/[N].[DIAMETER])
Code:SELECT DrivePulley.PartNo, DrivePulley.Diameter, NeutralPulley.PartNo, NeutralPulley.Diameter, [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] AS RATIO FROM Pullies as DrivePulley, Pullies as NeutralPulley where [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER] between 2.1 .4 and 2.1 +.4 order by abs(2.1 [DRIVEPULLEY].[Diameter]/[NEUTRALPULLEY].[DIAMETER])
for presentation
... D.Diameter / N.DIAMETER AS Ratio
for ease of writing I prefer to use short tabel aliases, hence why I used D for the drive pulley, and N for the neutral pulley
the where clause can be expanded to add other criteria as suyggested above, or if you find there are performnace issues (its taking a long time to find waht you want, then consider indexing say the diameter column AND addign additioanml criteria
such as
Code:...where [D].[Diameter]/[N].[DIAMETER] between 2.1 .4 and 2.1 +.4 AND [D].[Diameter] > [N].[DIAMETER]...
I'd rather be riding on the Tiger 800 or the Norton