# Thread: Compare All Combinations in an Expression Between Two Different Tables

1. Registered 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,000-5,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.
********************************************************************************************************```
My questions is how do I approach writing the code to look at comparing the combinations between the two tables. I need to have some guidance on how I can tackle this problem. Any help is great.

## " Originally Posted by rhuseman A pulley can be used as a drive or neutral pulley. There is no difference. 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])``` in fact you could use 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])``` an alias is a way of giving soemthing a different name, either for presentation, documentation or ease of writing / style of SQL 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]...``` "

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
as 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```
GIVES:-
Code:
```DrivePulley.PartNo	DrivePulley.Diameter	NeutralPulley.PartNo	NeutralPulley.Diameter	RATIO
100001	10	200001	5	2
100003	14	200002	7	2```
You can refine the query as required by altering the between (BETWEEN 2 AND 2.25)
...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```
the problem is setting the target range, and thats ticky without writing the query on the fly OR using a parameter query.

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```
say you redefine the query using parameters
\$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])```
...say the \$target was 2.1 and the tolerance .4 then
...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])```
...the results would be:-
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/en-us/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 ratio
Last edited by healdem; 06-23-15 at 06:30.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
incidentally 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 matter

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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by rhuseman
A pulley can be used as a drive or neutral pulley. There is no difference.
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])```
in fact you could use
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])```
an alias is a way of giving soemthing a different name, either for presentation, documentation or ease of writing / style of SQL
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]...```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•