• I have a table contracts that is related to services table.
• A contract may have multiple services.
• A service may have a device associated (through the device_id fk).

I need to have a list of contract_id and

1. YES: In case there is at least one service that has a device associated (device_id <> NULL)
2. NO, otherwise

How could I do this?

CodePudding user response：

You need to get all records from services where (device_id <> NULL) then use them to get final results.

SELECT 
  c.contract_id, IF(ISNULL(s.contract_id), 'NO', 'YES')
FROM
  contracts AS c
  LEFT JOIN (
    SELECT DISTINCT contract_id FROM services WHERE NOT ISNULL(device_id)
  ) AS s ON c.contract_id = s.contract_id

We use LEFT JOIN to get all records from contracts table.