SQL - Select Column Multiple Times

Question

I have 3 tables:

SystemInfo

WorkstationID
WorkstationName
Manufacturer
OS

SoftwareInfo

WorkstationID
SoftwareID

SoftwareList

SoftwareID
SoftwareName
SoftwareVersion

Each workstationID has about 20 SoftwareID rows (Reader, Flash, Java, etc.) I would like the output to look similar to 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 to query SoftwareList multiple times and filter each query so that it only returns the SoftwareVersion that is relative to the current WorkstationID.

This is the code I have so far, which won't execute:

select 
  systeminfo.WorkstationName, 
  systeminfo.OS, 
  systeminfo.Manufacturer,
  (Select SoftwareList.SoftwareVersion 
   from systeminfo 
        join softwareinfo on systeminfo.workstationid = softwareInfo.workstationID 
        JOIN SoftwareList on softwareinfo.softwareid = softwarelist.softwareid 
   where SoftwareName = 'Reader' 
         and softwareinfo.workstationid = systeminfo.workstationid) as "Reader" 
from 
  systeminfo 
  join softwareinfo on systeminfo.workstationid = softwareInfo.workstationID 
  JOIN SoftwareList on softwareinfo.softwareid = softwarelist.softwareid

your query references SoftwareInfo.FILEVERSION, but I don't see a FILEVERSION in the SoftwareInfo table? — Greg, Jun 18 '14 at 04:15
just refer [this](http://stackoverflow.com/a/11751905/3682599) — Vivek S., Jun 18 '14 at 04:48
My apologies, that was a typo when I entered it on stackoverflow. FileVersion and SoftwareVersion are the same when I tried executing locally. — ChemistryTruck, Jun 18 '14 at 05:06

score 0 · Accepted Answer · answered Jun 18 '14 at 04:33

taking some gueses here, the FILEVERSION is SoftwareVersion. Case doesn't matter (I don't think, if it does, then I need to update this). Also, these are outer joins, so, if the column doesn't exist, like, if you are missing the 'Flash' version you will still get a row. If that isn't desired change the 'left join' to 'join'. then you will only get tuples if all of the softwares exist in your query. I don't know if that makes sense. I'm sorry I didn't test this. Hope it helps.

select
    s.WorkstationName,
    s.OS,
    s.Manufacturer,
    sr.softwareversion as "Reader",
    sf.softwareversion as "Flash"
  from
    systeminfo s
left join
    softwareinfo sor on (sor.workstationid = s.workstationid)
left join
    softwarelist sr on (sr.softwareid = sor.softwareid and sr.softwarename = 'Reader')
left join
    softwareinfo sof on (sof.workstationid = s.workstationid)
left join
    softwarelist sf on (sf.softwareid = sof.softwareid and sf.softwarename = 'Flash')

Using this method, `SoftwareInfo` probably doesn't need to be joined multiple times, only `SoftwareList` does. — Andriy M, Jun 18 '14 at 17:29
Thank you, Greg! This worked perfectly. As soon as I have a higher reputation I will mod you up. — ChemistryTruck, Jun 19 '14 at 00:04
thanks. check out the @Andriy M comment. You can inner join the softwareinfo table once, then left join all of the rest. Also, hector above points to crosstab, i didn't know it existed, very cool (requires installing an extension). — Greg, Jun 19 '14 at 02:35

SQL - Select Column Multiple Times

1 Answers1