unloading an additional column for one specific value specified in the "WHERE" condition-CodePudding

From such a request:

WITH t as(
   SELECT a.Id,
      a.Name,
      a.Description,
      b.Title,
      c.Role
   FROM aTable AS a
      LEFT JOIN bTable AS b ON a.Id=b.uuid
      LEFT JOIN cTable AS c ON b.uuid=c.roleId
   WHERE c.role='major' OR c.role='captian'
   GROUP BY a.Id)
SELECT t.Id,
   t.Name,
   t.Description,
   t.Title,
   t.Role,
   d.Email
FROM t
LEFT JOIN dTable AS d ON t.Id=d.Guid

I receive mail for both roles (for major and for captain). Q: How to get mail for captain only?

I attach the result of my script

enter image description here and the result I expect

enter image description here

CodePudding user response：

Use an IF() expression.

WITH t as(
   SELECT a.Id,
      a.Name,
      a.Description,
      b.Title,
      c.Role
   FROM aTable AS a
      INNER JOIN bTable AS b ON a.Id=b.uuid
      INNER JOIN cTable AS c ON b.uuid=c.roleId
   WHERE c.role='major' OR c.role='captain'
   GROUP BY a.Id)
SELECT t.Id,
   t.Name,
   t.Description,
   t.Title,
   t.Role,
   IF(c.role = 'captain', d.Email, '') AS Email
FROM t
LEFT JOIN dTable AS d ON t.Id=d.Guid