CODEWITHSUNDEEP
mysql
Bruno
Bruno

Reputation: 1055

Join two select in mysq

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

Upvotes: 1

Views: 53

Answers (2)

Ozan Sen
Ozan Sen

Reputation: 2615

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;

Upvotes: 1

Don Hardman
Don Hardman

Reputation: 1

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'

Upvotes: 0

Related Questions