alpathfinder

SELECT
  trnBP.text as "BluePrint"
  , trnProd.text as "Product"
  , trnMat.text as "Requirements"
  , mat.quantity
FROM
  public."industryActivityProducts" as prod
  , public."trnTranslations" as trnBP
  , public."trnTranslations" as trnProd
  , public."industryActivityMaterials" as mat
  , public."trnTranslations" as trnMat
WHERE
  prod."typeID" = mat."typeID"
  AND trnBP."keyID" = prod."typeID"
  AND trnBP."tcID" = 8
  AND trnBP."languageID" = 'ru'
  AND trnProd."keyID" = prod."productTypeID"
  AND trnProd."tcID" = 8
  AND trnProd."languageID" = 'ru'
  AND trnMat."keyID" = mat."materialTypeID"
  AND trnMat."tcID" = 8
  AND trnMat."languageID" = 'ru'
order by
  trnBP.text
  , trnProd.text
  , trnMat.text
;

select
 d."BluePrint"
 , d."Product"
 , max(case when d.matID = 34 then d.quantity else 0 end) AS "Tritanium"
 , max(case when d.matID = 35 then d.quantity else 0 end) AS "Pyerite"
 , max(case when d.matID = 36 then d.quantity else 0 end) AS "Mexallon"
 , max(case when d.matID = 37 then d.quantity else 0 end) AS "Isogen"
 , max(case when d.matID = 38 then d.quantity else 0 end) AS "Nocxium"
 , max(case when d.matID = 39 then d.quantity else 0 end) AS "Zydrine"
 , max(case when d.matID = 40 then d.quantity else 0 end) AS "Megacyte"

from (
	SELECT 
	  trnBP.text as "BluePrint"
	  , trnProd.text as "Product"
	  , mat."materialTypeID" as matID
	  , mat.quantity
	FROM 
	  public."industryActivityProducts" as prod
	  , public."trnTranslations" as trnBP
	  , public."trnTranslations" as trnProd
	  , public."industryActivityMaterials" as mat
	WHERE 
	  prod."typeID" = mat."typeID"
	  AND trnBP."keyID" = prod."typeID"
	  AND trnBP."tcID" = 8
	  AND trnBP."languageID" = 'ru'
	  AND trnProd."keyID" = prod."productTypeID"
	  AND trnProd."tcID" = 8
	  AND trnProd."languageID" = 'ru'
  ) as d

group by
 d."BluePrint"
 , d."Product"

having
 max(case when d.matID = 34 then d.quantity else 0 end) > 0
 or max(case when d.matID = 35 then d.quantity else 0 end) > 0
 or max(case when d.matID = 36 then d.quantity else 0 end) > 0
 or max(case when d.matID = 37 then d.quantity else 0 end) > 0
 or max(case when d.matID = 38 then d.quantity else 0 end) > 0
 or max(case when d.matID = 39 then d.quantity else 0 end) > 0
 or max(case when d.matID = 40 then d.quantity else 0 end) > 0

order by
 d."BluePrint"
 , d."Product"
;