Question 285833 in Mysql Question posted in
The official MySQL documentation can be found here.

Join two tables with different fields in mysql

I want to join two tables with different fields in mysql. I have the following data in the tables:

AQuenteSan AFriaSan Realizacao
0.58 0.61 2023-11-14
0.52 0.64 2023-11-15
dioxcloro Realizacao
0.02 2023-11-14
0.10 2023-11-15

Now I’m trying to join the data like this:

WITH
exp1 AS (SELECT AQuenteSan, AFriaSan, Realizacao FROM raddb.Reg_Residual WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15'),
exp2 AS (SELECT dioxcloro, Realizacao AS Realizacao1 FROM raddb.Reg_Dioxido WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15')
SELECT DISTINCT * FROM exp1 JOIN exp2;

And this is the result I got when executing the query shown above:

AQuenteSan AFriaSan Realizacao dioxcloro Realizacao1
0.58 0.61 2023-11-14 0.10 2023-11-15
0.58 0.61 2023-11-14 0.02 2023-11-14
0.52 0.64 2023-11-15 0.10 2023-11-15
0.52 0.64 2023-11-15 0.02 2023-11-14

But the result I want is just this:

AQuenteSan AFriaSan Realizacao dioxcloro Realizacao1
0.58 0.61 2023-11-14 0.02 2023-11-14
0.52 0.64 2023-11-15 0.10 2023-11-15

How can I get this result?

Tags:

2

Answers


  1. 0

    You need a column to join them on. In your case, you could perform a LEFT JOIN:

    SELECT * from raddb.Reg_Residual r LEFT JOIN raddb.Reg_Dioxido d ON d.Realizacao = r.Realizacao WHERE r.Realizacao BETWEEN '2023-11-14' AND '2023-11-15'

    This will give you the result you want. The LEFT JOIN makes sure, you only get one instance of an entry in the first table.

    Login or Signup to reply.
  2. 0 
     SELECT raddb.Reg_Residual.AQuenteSan, raddb.Reg_Residual.AFriaSan, 
  raddb.Reg_Residual.Realizacao,raddb.Reg_Dioxid.dioxcloro,
  raddb.Reg_Dioxid.Realizacao1
 FROM raddb.Reg_Residual
 INNER JOIN raddb.Reg_Dioxid ON 
  raddb.Reg_Residual.Realizacao1=raddb.Reg_Dioxid.Realizacao1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search