Reputation: 81
I'm creating an IT Service Management system for schools. I need to retrieve a query from an SQL database that shows basic details of a device, along with the ID of the owner that is assigned to that device. The 'devices' table lists all computers and the 'assignments' table specifies which user is assigned to each device (based on assetID).
The problem is that I'm not sure how to refer to the 'assetID' value for the current row from the main query.
SELECT
devices.assetID,
devices.make,
devices.model,
TotalServices=Count(services.id),
AssignedOwner=(SELECT assignments.studentID FROM devices INNER JOIN assignments ON devices.assetID=assignments.assetID WHERE assignments.assetID=<VALUE OF ASSET ID FOR CURRENT ROW>)
FROM devices
LEFT JOIN services
ON services.assetID = devices.assetID
GROUP BY
devices.assetID,
devices.make,
devices.model
I'm not sure how I could reference the value of the assetID for the current row in order to retrieve the assigned user's ID...
assignments.assetID=<VALUE OF ASSET ID FOR CURRENT ROW>
Thanks in advance for any guidance!
Upvotes: 0
Views: 42
Reputation: 13248
I'm thinking you want to add a join to the assignments table, like:
select devices.assetid,
devices.make,
devices.model,
count(services.id) as totalservices,
assignments.studentid as assignedowner
from devices
left join assignments
on devices.assetid = assignments.assetid
left join services
on services.assetid = devices.assetid
group by devices.assetid,
devices.make,
devices.model,
assignments.studentid
The left join
or left outer join
(same thing) is only needed if a device might not have a corresponding asset on the assignments
and services
table, respectively. You should change left join
to just join
if the asset always has a value on those other tables (independently considering each one).
Upvotes: 1
Reputation: 1854
If I understand correctly would this not be what you're looking for?
SELECT
devices.assetID,
devices.make,
devices.model,
TotalServices=Count(services.id),
assignments.studentID AS AssignedOwner
FROM devices
INNER JOIN assignments ON devices.assetID=assignments.assetID
LEFT JOIN services
ON services.assetID = devices.assetID
GROUP BY
devices.assetID,
devices.make,
devices.model
Do you have example data an expected results to test against?
Upvotes: 0