Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Shelane French
Added: Jan 24, 2020 9:42 PM
Views: 7
Tags: mysql
  1. -- this example is not currently used since the benefits code was removed in LCI
  2. SELECT title,
  3.     black_belts.name,
  4.     pipID,
  5.     obj,
  6.     projStat,
  7.     dateEnd,
  8.     concat(PAD, '/', dir) AS PADdir,
  9.     concat(PAD_second, '/', dir_second) AS PADdir_second,
  10.     f.FA,
  11.     projLead,
  12.     IF(bencount>0, 'Y', 'N') AS benexists
  13. FROM projects p
  14. INNER JOIN FAs f ON p.FA=f.code
  15. LEFT JOIN (SELECT projID, COUNT(*) AS bencount FROM proj_benefits GROUP BY projID) AS benefits ON p.ID=benefits.projID
  16. INNER JOIN ( SELECT
  17.             GROUP_CONCAT(CONCAT(nameFirst, ' ', nameLast)
  18.             ORDER BY nameLast SEPARATOR ' ') AS name,
  19.             projID FROM users INNER JOIN proj_people ON oun=username
  20.             WHERE level = 'Black Belt' GROUP BY projID) AS black_belts
  21.             ON p.ID = black_belts.projID
  22. ORDER BY title
  23.  
  24.  
  25.  
  26. -- This example from LCI uses a case on a field and returns other field values based on the condition:
  27.  
  28. SELECT projects.projID,
  29.     title,
  30.     projType,
  31.     projStat,
  32.     projLead,
  33.     (CASE projLead WHEN 'Black Belt' THEN leadBlack WHEN 'Yellow Belt' THEN leadYellow END) AS lead,  
  34. FROM projects
  35. LEFT JOIN
  36.         (SELECT CONCAT(nameFirst, ' ', nameFirst) AS leadBlack, projID AS pID  
  37.         FROM proj_people INNER JOIN users ON proj_people.username=users.oun
  38.         WHERE level='Black Belt' GROUP BY projID)
  39.         AS blackLead ON projects.ID=blackLead.pID
  40.      
  41. LEFT JOIN
  42.         (SELECT CONCAT(nameFirst, ' ', nameFirst) AS leadYellow, projID AS pID
  43.         FROM proj_people INNER JOIN users ON proj_people.username=users.oun
  44.         WHERE level='Yellow Belt' GROUP BY projID)
  45.         AS yellowLead ON projects.ID=yellowLead.pID
  46.  
  47. ORDER BY lead