CODEWITHSUNDEEP
sql
tpag26
tpag26

Reputation: 81

SQL Subquery - Referring to an outer value within the subquery's 'WHERE' clause

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

Answers (2)

Brian DeMilia
Brian DeMilia

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

Moss Palmer
Moss Palmer

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

Related Questions