[HELP] SQL užklausa (dvi lentelės)

[mindux9]

Yra dvi lentelės: "Device" ir "InstalledApp", abejose lentelėse yra duomenys apie devaiso id (ne ID), kurio įrašas yra toks pvz: "SEC1D467415D73C9"

 

Device lentelėje jis aprašomas kaip: "DeviceID"

o

InstalledApp lentelėje kaip: "Device"

 

Gal kaip nors išeitu pagal šį devaiso id'ą išgauti devaiso vardą, kuris "Device" lentelėje aprašomas kaip "Name". Naudojant TIK SQL užklausą?

 

Dabar užklausa atrodo tokia:

SELECT InstalledApp.Name, InstalledApp.VersionName,
CASE
WHEN InstalledApp.Device = 'SEC1346A6E82FDD1' THEN 'HJS 834'
WHEN InstalledApp.Device = 'SEC1DCCA4E704369' THEN 'JFA 291'
WHEN InstalledApp.Device = 'SEC1D467415D73C9' THEN 'JCV 498'
WHEN InstalledApp.Device = 'SEC1BD01D7CAC207' THEN 'JFA 341'
WHEN InstalledApp.Device = 'SEC192E9BEB17A4E' THEN 'JEZ 884'
WHEN InstalledApp.Device = 'SEC1A2FA14C377C5' THEN 'TEST'
WHEN InstalledApp.Device = 'SEC1695DEEEA2ACD' THEN 'GOV 873'
WHEN InstalledApp.Device = 'SEC167DC19014D68' THEN 'JEZ 892'
WHEN InstalledApp.Device = 'SEC1AC3528A6A1B0' THEN 'GUJ 981'
WHEN InstalledApp.Device = 'SEC1327BA5556C09' THEN 'HJS 835'
WHEN InstalledApp.Device = 'SEC1936A82504329' THEN 'GZZ 181'
WHEN InstalledApp.Device = 'SEC158ED635644C4' THEN 'JFA 391'
WHEN InstalledApp.Device = 'SEC1085492FD8150' THEN 'JFA 139'
WHEN InstalledApp.Device = 'SEC122A71590E8E0' THEN 'JEK 290'
WHEN InstalledApp.Device = 'SEC1F8C286118143' THEN 'JFA 393'
WHEN InstalledApp.Device = 'SEC15D0AF25F9562' THEN 'GUU 175'
WHEN InstalledApp.Device = 'SEC1B620DBD5DD82' THEN 'GUJ 973'
WHEN InstalledApp.Device = 'SEC122D558593BA6' THEN 'JEZ 893'
WHEN InstalledApp.Device = 'SEC131EEEA967B91' THEN 'HCJ 923'
WHEN InstalledApp.Device = 'SEC1119364FDC743' THEN 'JJS 577'
WHEN InstalledApp.Device = 'SEC13FD77F74005C' THEN 'JJS 578'
WHEN InstalledApp.Device = 'SEC135384C664E30' THEN 'GUU 173'
WHEN InstalledApp.Device = 'SEC1D79CB76024FC' THEN 'GZZ 180'
WHEN InstalledApp.Device = 'SEC1DF7ED564A8C8' THEN 'JJS 574'
WHEN InstalledApp.Device = 'SEC192C358A73904' THEN 'JFA 392'
WHEN InstalledApp.Device = 'SEC1174FF638BB80' THEN 'HJS 831'
WHEN InstalledApp.Device = 'SEC1FA4359208CDE' THEN 'JDU 498'
WHEN InstalledApp.Device = 'SEC18ADEC882C9CC' THEN 'JJS 579'
WHEN InstalledApp.Device = 'SEC1B650BC6A08E3' THEN 'JFA 394'
WHEN InstalledApp.Device = 'SEC11AFD8B2B85A0' THEN 'HJS 821'
WHEN InstalledApp.Device = 'SEC11A896AA1868B' THEN 'GUJ 969'
WHEN InstalledApp.Device = 'SEC1ECF5670FB077' THEN 'HJS 819'
WHEN InstalledApp.Device = 'SEC155AC61FC9CE2' THEN 'JFA 285'
WHEN InstalledApp.Device = 'SEC17C573D93CD00' THEN 'JFA 287'
WHEN InstalledApp.Device = 'SEC16F7D70B37A0A' THEN 'JFA 347'
WHEN InstalledApp.Device = 'SEC18F8EA79C8735' THEN 'HCJ 532'
END AS VALSTYBINIS
FROM InstalledApp
WHERE InstalledApp.AppId LIKE 'com.lat.fleetmaster%'

Noriu kad nereiktu "žaistis" su tuo CASE.

[bebras]

10 dienų veluoju, tai nežinau kiek tau tai dar aktualu.

select InstalledApp.*, Device.Name from InstalledApp left join Device on Device.DeviceID = InstalledApp.Device;

Šita užklausa paims viską iš InstalledApp, ir prie kiekvienos eilutės pridės atitinkamą "Name" iš "Device" lentelės.

[mindux9]

Pasidariau galutini tokį:

select InstalledApp.Name AS FleetMaster, InstalledApp.VersionName, Device.Name from InstalledApp left join Device on Device.DeviceID = InstalledApp.Device WHERE InstalledApp.AppId LIKE 'com.lat.fleetmaster%';

Pirmiau ir bandydavau daryt kažka su JOIN'ais, bet nepavykdavo.

 

Ačiū.

Redaguota Lapkričio 18, 2016, nario mindux9