sqlinformixcucm

Combine 3 queries to one in Informix SQL


I have 3 sql queries that I would like to combine in one.

I know the value 'UKLN_8945_UDP' that I use in 1st query, that 1st query gives me two values that I can't read so I need to run 2 other queries to return values that I can read.

The 1st query is

run sql SELECT EMD.fkdevice,EMD.fkEnduser FROM extensionmobilitydynamic EMD WHERE EMD.fkdevice_currentloginprofile = (SELECT d1.pkid FROM device d1 WHERE d1.name = 'UKLN_8945_UDP')

That query returns 2 values: fkdevice: f3b95ea0-63f8-49d1-911f-cacdd68d0967 fkenduser: 5a71fc04-d348-8115-9ff9-ea78f38b06fc

The 2nd query is using the fkdevice from the 1st query

run sql select D.name SEPDname from device D where D.pkid = 'f3b95ea0-63f8-49d1-911f-cacdd68d0967'

That query returns 1 value: sepdname: SEP0057D2C01D0B

The 3rd query is using the fkenduser from the 1st query

run sql select E.userid from enduser E where E.pkid = '5a71fc04-d348-8115-9ff9-ea78f38b06fc'

That query returns 1 value: userid: UKLN


Solution

  • SELECT   EMD.fkdevice
    ,        EMD.fkEnduser
    ,        D2.name
    ,        E.userid
    FROM     device D1
    JOIN     extensionmobilitydynamic EMD 
    ON       EMD.fkdevice_currentloginprofile = D1.pkid
    JOIN     device D2
    ON       D2.pkid = EMD.fkdevice
    JOIN     enduser E
    ON       E.pkid = EMD.fkEnduser
    WHERE    D1.name = 'UKLN_8945_UDP'