SQL Sentence help select multiple tables-CodePudding

I have this setence that select a Product NAME from my table Produto and select the quantity from other table named Contagens where the Produto.EANU = Contagens.PRODUTO but I also have some QTD that are related to EANP istead of EANU and I want to them be shown as ARM instead of QTD.

Is there any way that I can get those 2 sentences in one ?

SELECT Produto.NAME, IFNULL(Contagens.QTD ,0) as QTD FROM Produto 
LEFT 
OUTER JOIN Contagens ON Produto.EANU = Contagens.PRODUTO 
ORDER BY NAME

SELECT Produto.NAME, IFNULL(Contagens.QTD ,0) as ARM FROM Produto 
LEFT 
OUTER JOIN Contagens ON Produto.EANP = Contagens.PRODUTO
ORDER BY NAME

CodePudding user response：

Just join them using aliases:

SELECT Produto.NAME, 
       IFNULL(EANU_Contagens.QTD ,0) as QTD, 
       IFNULL(EANP_Contagens.QTD ,0) as ARM
  FROM Produto 
       LEFT OUTER JOIN Contagens AS EANU_Contagens ON Produto.EANU = Contagens.PRODUTO
       LEFT OUTER JOIN Contagens AS EANP_Contagens ON Produto.EANP = Contagens.PRODUTO
ORDER BY NAME

CodePudding user response：

Combine the conditions, and then use IF in the SELECT list to test which condition was matched.

SELECT p.Name, IF(p.EANU = c.PRODUTO, Contagens.QTD, 0) AS QTD, IF(p.EANP = c.PRODUTO, Contagents.QTD, 0) AS ARM
FROM Produto AS p
LEFT JOIN Contagens AS c ON c.PRODUTO IN (p.EANU, p.EANP)
ORDER BY p.Name