Results 1 to 5 of 5
  1. #1
    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.

  2. Best Answer
    Posted by healdem

    "
    Quote Originally Posted by rhuseman View Post
    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]...
    "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    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?

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rhuseman View Post
    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]...
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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