Join two select in mysq-CodePudding

I intend to join two selects, but I don't intend to use union or union all, because I intend to add columns to the right side of the query. I will explain the two queries.

SELECT N_utente, Ano, Mes, Farmacia
FROM raddb.Areceber
where N_utente <> '0' AND Farmacia <> '0.00' AND Mes = '1'

returns the following:

N_utente, Ano, Mes8, Farmacia8
'602',  '2022', '9', '16.23'

Then I have the second query:

SELECT N_utente, Ano, Mes, Cabeleireiro
FROM raddb.Areceber
where N_utente <> '0' AND Cabeleireiro <> '0.00' AND Mes = '1'

returns the following:

N_utente, Ano, Mes9, Cabeleireiro9
'716',  '2022', '10', '16.00'

Now I want to join the two queries, where I have the following result:

N_utente, Ano, Mes8, Farmacia8, Mes9, Cabeleireiro9
'602',  '2022', '9', '16.23',   '10', '16.00'

Is it possible to combine the two queries like this? union does not allow to join the two queries in this way

CodePudding user response：

You can use CTE'S if your version is 8.0 and more:

You can check your version: SELECT VERSION();

Then Code :

WITH cte1 AS (SELECT N_utente, Ano, Mes, Farmacia
FROM Areceber
where N_utente <> '0' AND Farmacia <> '0.00' AND Mes = '1')
,cte2 AS (SELECT N_utente, Ano, Mes, Cabeleireiro
FROM Areceber
where N_utente <> '0' AND Cabeleireiro <> '0.00' AND Mes = '1')
SELECT cte1.N_utente, cte1.Ano, cte1.Mes AS Mes8, cte1.Farmacia AS Farmacia, cte2.mes AS Mes9, cte2.Cabeleireiro AS Cabeleireiro9
FROM cte1 JOIN cte2 ON cte1.Ano = cte.Ano;

CodePudding user response：

Just update your WHERE condition

SELECT N_utente, Ano, Mes, IF(Farmacia <> '0.00', Farmacia, Cabeleireiro) AS something
FROM raddb.Areceber
where N_utente <> '0' AND (Farmacia <> '0.00' OR Cabeleireiro <> '0.00') AND Mes = '1'