postgresql - SQL - Select Column Multiple Times -

postgresql - SQL - Select Column Multiple Times -

i have 3 tables:

systeminfo

workstationid workstationname manufacturer os

softwareinfo

workstationid softwareid

softwarelist

softwareid softwarename softwareversion

each workstationid has 20 softwareid rows (reader, flash, java, etc.) output similar this:

workstationid | os | manufacturer| reader version | flash version | java version desktop01 | w7 | lenovo | 11.0.7 | 14.x | 8.x

i cannot figure out how query softwarelist multiple times , filter each query returns softwareversion relative current workstationid.

this code have far, won't execute:

select systeminfo.workstationname, systeminfo.os, systeminfo.manufacturer, (select softwarelist.softwareversion systeminfo bring together softwareinfo on systeminfo.workstationid = softwareinfo.workstationid bring together softwarelist on softwareinfo.softwareid = softwarelist.softwareid softwarename = 'reader' , softwareinfo.workstationid = systeminfo.workstationid) "reader" systeminfo bring together softwareinfo on systeminfo.workstationid = softwareinfo.workstationid bring together softwarelist on softwareinfo.softwareid = softwarelist.softwareid

taking gueses here, fileversion softwareversion. case doesn't matter (i don't think, if does, need update this). also, these outer joins, so, if column doesn't exist, like, if missing 'flash' version still row. if isn't desired alter 'left join' 'join'. tuples if of softwares exist in query. don't know if makes sense. i'm sorry didn't test this. hope helps.

select s.workstationname, s.os, s.manufacturer, sr.softwareversion "reader", sf.softwareversion "flash" systeminfo s left bring together softwareinfo sor on (sor.workstationid = s.workstationid) left bring together softwarelist sr on (sr.softwareid = sor.softwareid , sr.softwarename = 'reader') left bring together softwareinfo sof on (sof.workstationid = s.workstationid) left bring together softwarelist sf on (sf.softwareid = sof.softwareid , sf.softwarename = 'flash')

sql postgresql