CODEWITHSUNDEEP
sqlsql-server
Mister Epic
Mister Epic

Reputation: 16743

Check existence of record in junction table

I have a device entity which may or may not have an owner depending on whether or not is has been sold:

DEVICE
======
Id
OwnerId (null)
DeviceTypeId (not null)

Owner
=====
Id
Name

Each type of device can have different capabilities, while each capability can be mapped to a multiple types, so I have a junction table.

DeviceType
====
Id
ModelNum

Capability
==========
Id
CapabilityName

Type_Capability
===============
Id
DeviceTypeId
CapabilityId

I want to write a query that will return the device's owner if it exists, plus whether the device has a capability with the name "overclock".

I have the following:

SELECT device.Id, owner.Name, ??? as [hasOverclock]
FROM Device device
LEFT OUTER JOIN Owner owner on owner.Id = device.ownerId
INNER JOIN DeviceType deviceType on deviceType.Id = device.DeviceTypeId
INNER JOIN TypeCapability typeCapability on typeCapability.DeviceTypeId = deviceType.Id
INNER JOIN Capability capability on capability.Id = typeCapability.CapabilityId 
WHERE device.Id = 100;

I'm unsure how I surface whether the device is overclockable in my projection.

Upvotes: 1

Views: 228

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You need an aggregation:

SELECT D.ID as DeviceID, O.Name as OwnerName,
       (CASE WHEN SUM(CASE WHEN C.CapabilityName = 'overclock' THEN 1 ELSE 0 END) > 0
             THEN 1
             ELSE 0
        END) as OverclockFlag
FROM Device D JOIN DeviceType
     DT
     ON D.DeviceTypeID = DT.ID LEFT OUTER JOIN
     Type_Capability TC 
     ON DT.ID = TC.DeviceTypeID LEFT OUTER JOIN
     Capability C
     ON TC.CapabilityID = C.ID LEFT OUTER JOIN
     Owner O
     ON D.OwnerID = O.ID
WHERE Device.ID = 100 
GROUP BY D.ID, O.Name ;

Note that you an remove the where and do this for all the device ids.

Upvotes: 1

Stan Shaw
Stan Shaw

Reputation: 3034

You want to use a left outer join - and add to the on clause the capability name 'overclock'. Then, use a case statement to determine whether or not that record exists - and return 'Yes' for when it does - and 'No' for when it doesn't. Also, I indented your query for clarity:

SELECT  device.Id, 
    owner.Name, 
    CASE 
        WHEN Capability.ID IS NULL THEN 'NO'
        ELSE 'YES' 
    END as [hasOverclock]
FROM Device device
LEFT OUTER JOIN Owner 
    on Owner.Id = Device.ownerId
INNER JOIN DeviceType 
    on DeviceType.Id = Device.DeviceTypeId
INNER JOIN TypeCapability 
    on TypeCapability.DeviceTypeId = DeviceType.Id
LEFT OUTER JOIN Capability 
    on Capability.Id = TypeCapability.CapabilityId 
    AND Capability.CapabilityName = 'Overclock'
WHERE device.Id = 100;

Please let me know if this works or not and I'll edit my answer.

NOTE: You need to keep the capability name check (for 'overclock') in the ON clause and NOT in the where clause. If it's in the where clause, you'll omit records that don't join, entirely (which is the same as using an inner join). Outer joins are the only time that moving a condition from the where to the on clause impacts the result set.

EDIT:

Because of the structure of your database, I think it's best to just use the following query. Since the 'OverClock' capabilityID isn't going to change - and since your query is specific to this value, I think it makes sense to use it in the query, making it much simpler:

DECLARE @OverClockCapabilityID int = 55

SELECT  D.Id as [DeviceID],
    O.Name as [OwnerName],
    CASE 
        WHEN TC.ID IS NULL THEN 'NO'
        ELSE 'YES'
    END AS [HasOverClock]
FROM Device D      //Should only be one record
JOIN DeviceType DT  //Should only be one record
    ON D.DeviceTypeID = DT.ID
LEFT OUTER JOIN Type_Capability TC  //Should only be one record, assuming you don't have duplicate records in this join table (which you shouldn't)
    ON DT.ID = TC.DeviceTypeID
    AND TC.CapabilityID = @OverClockCapabilityID //This eliminates other capabilities, which we don't care about in this query
LEFT OUTER JOIN Owner O
    ON D.OwnerID = O.ID

I hope this works for you!

Upvotes: 1

Related Questions