SQL Puzzle

General
1

I have a table the contains device information

  • device_id [Integer]
  • width [double]
  • height [double]
    these values are relative to PORTRAIT mode
    the table contains multiple device resolutions

I need to create another table based solely on the one described above that is each device related to every other device, but with ratio information instead

For example
device #1 is 5120 x 2880
device #2 is 1280 x 1024

the desired results would be a table

  • fromDevice [Integer]
  • fromOrient [Char]
  • toDevice [Integer]
  • toOrient [Char]
  • hMult [double]
  • vMult [double]
// device, orient, toDev,toOrient, hMult, vMult
1 , P , 2 , P ,  4.00  ,  2.8125  // 5120/1280 , 2880/1024 - port to port
1 , P , 2 , L ,  5.00  ,  2.25      // 5120/1024 , 2880/1280  - port to land
1 , L , 2 , P ,  2.25  ,  5.00     // 2880/1280 , 5120/1024 - land to port
1 , L , 2 , L ,   2.8125  , 4

the purpose is to create a table for conversion of coordinates on one device/orientation to another device/orientation
so the first example would result in taking a X coor from dev#1 and dividing by 4.00 to get the relative coor on Dev 2

since these values may be used hundreds of times in the app, it would be better to pre-calculate them rather than on-the-fly

2

Ok… I think this works… can anyone find a flaw? or even a “better way”?

SELECT a.device_id AS fromDEV,
       'P' as fromOrient,
       b.device_id AS toDEV,
       'P' AS toOrient,
       CAST(a.deviceWidth  AS DOUBLE)/CAST(b.deviceWidth  AS DOUBLE) as hMult,
       CAST(a.deviceheight AS DOUBLE)/CAST(b.deviceheight AS DOUBLE) as vMult
  FROM devices a, devices b
UNION
SELECT a.device_id AS fromDEV,
       'P' as fromOrient,
       b.device_id AS toDEV,
       'L' AS toOrient,
       CAST(a.deviceWidth  AS DOUBLE)/CAST(b.deviceHeight  AS DOUBLE) as hMult,
       CAST(a.deviceheight AS DOUBLE)/CAST(b.deviceWidth   AS DOUBLE) as vMult
  FROM devices a, devices b
UNION
SELECT a.device_id AS fromDEV,
       'L' as fromOrient,
       b.device_id AS toDEV,
       'P' AS toOrient,
       CAST(a.deviceHeight AS DOUBLE)/CAST(b.deviceWidth  AS DOUBLE) as hMult,
       CAST(a.deviceWidth  AS DOUBLE)/CAST(b.deviceHeight AS DOUBLE) as vMult
  FROM devices a, devices b
UNION
SELECT a.device_id AS fromDEV,
       'L' as fromOrient,
       b.device_id AS toDEV,
       'L' AS toOrient,
       CAST(a.deviceHeight AS DOUBLE)/CAST(b.deviceHeight  AS DOUBLE) as hMult,
       CAST(a.deviceWidth  AS DOUBLE)/CAST(b.deviceWidth AS DOUBLE) as vMult
  FROM devices a, devices b

Note… it seems SQLite returns a division in the same affinity as the fields, hence the need to CAST

3