# SQL Puzzle

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

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