Create a report from a join of two tables in MSSQL

Question on how to use and MSSQL join to create a report from a join of two tables

I have two tables in the AAA database: aaa.dbo.machines and aaa.dbo.machineDetails. Based on the info below, how can I write a query that joins the two tables and queries the machineID, machineName, and location. The machineID in the first table matches entityID in the second table.

# machines table:
machineID, machineName
001, fileserver001
002, printserver 001

# machineDetails:
entityID, propertyName,propertyValue
001, status, on
001,uptime, 33min
002,uptime,22 days
Who is Participating?
Nitin SontakkeDeveloperCommented:
You can keep on self joining the MachineDetails table for each distinct propertyName and pick it's corrosponding propertyValue, as shown below:

declare @machines table
   [machineID] varchar(3) not null primary key clustered
  ,[machineName] varchar(50) not null

insert into @machines (machineID, machineName) values 
 ('001', 'fileserver001')
,('002', 'printserver 001')

declare @machineDetails table
   [entityID] varchar(3) not null
  ,[propertyName] varchar(50) not null
  ,[propertyValue] varchar(50) not null

insert into @machineDetails (entityID, propertyName,propertyValue) values
 ('001','status', 'on')
,('001','location', 'zone1')
,('001','uptime', '33min')
,('002','status', 'off')
,('002','location', 'zone3')
,('002','uptime', '22 days')

select m.*
  ,l.propertyValue [location]
  ,s.propertyValue [status]
  ,u.propertyValue [uptime]
from @machines m
left outer join @machineDetails l on m.[machineID] = l.[entityID] and l.[propertyName] = 'location'
left outer join @machineDetails s on m.[machineID] = s.[entityID] and s.[propertyName] = 'status'
left outer join @machineDetails u on m.[machineID] = u.[entityID] and u.[propertyName] = 'uptime'

Open in new window

Hope it helps!
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would say that you have a database design issue.
Why don't redesign the machinedetails to be something like:
# machineDetails:
entityID, status, location, uptime
001, on, zone1 33min
002,off,zone3,22 days

Then your SELECT will be very simple and faster to run:
SELECT m.machineName, d.status, d.location, d.uptime
FROM machines m
    INNER JOIN machineDetails d ON m.machineID=d.entityID

Open in new window

LN41Author Commented:
Both solutions are good. I've been trying different things to get a useful report. It's a vendor's database that provides no reporting so I'm trying to query the db myself. Through testing, I've found that I can get everything from just one of the tables but getting the output formatted correctly is a new challenge. I'll submit a new question to start clean from that angle.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.