SQL how to join two tables with same column names but different data in SQL

Question

In need you help with that.

I have two databases with 42 column names and need merge to another database with the same 42 columns with the same column names but different values.

DT1

Column0 | Column1 | column3 | column… | column42
1234 example 345 final1
1234 example 567 final3
1234 example 789 final5
12345 example 7890 final9
12345 example 890 final10

DT2

Column0 | Column1 | column3 | column… | column42
4567 example 345 final1
4567 example 567 final3
4567 example 789 final5
45678 example 7890 final9
45678 example 890 final10

DT Result

Column0 | Column1 | column3 | column… | column42
1234 example 345 final1
1234 example 567 final3
1234 example 789 final5
12345 example 7890 final9
12345 example 890 final10
4567 example 345 final1
4567 example 567 final3
4567 example 789 final5
45678 example 7890 final9
45678 example 890 final10

Thanks!

my code

#directory where report results will be saved
report_result = r"path\report_result_%s.csv"%today
report_result_sql_query =""" Select b.column1, b.column2, b.column3, ... b.column42
,a.column1, a.column2, a.column3, ... a.column42
FROM table1 b
FULL OUTER JOIN table2 a
ON a.column1=b.column2
WHERE b.column2 like '%ff%'AND (a.column3 is null or a.column3= '0' or a.column3= ' ') AND a.column2 like '%ff%' AND b.column5 >= '{}' AND a.column5>= '{}'""".format(day,yyday)
print(report_result_sql_query)
report_result_colour = web_service(report_result_sql_query)
print(report_result_colour.shape)
ORC_genesis1_colour.to_csv(report_results_final, index=False)

It is not giving an error however it is giving zero rows and columns but when I run both tables in two separate SQL queries I get results so I assume something is wrong in my join

'It is not giving an error' - MYSQL does not have FULL JOIN so either this statement is incorrect or you are not using mysql - mssql/sql-server maybe. Please check and correct tags — P.Salmon
– P.Salmon, Commented Nov 14, 2022 at 8:32
How are you trying to merge? Maybe a UNION makes more sense? — StonedTensor
– StonedTensor, Commented Nov 14, 2022 at 8:33
I am using sql in python not sure whether it have the same characteristics as mysql — ahmad
– ahmad, Commented Nov 14, 2022 at 8:35
@ahmad which database management systems (DBMS) are you querying from? Is there a way you could find out? — StonedTensor
– StonedTensor, Commented Nov 14, 2022 at 8:35

MT0 · Accepted Answer · 2022-11-14 10:24:53Z

1

Don't use FULL OUTER JOIN, use UNION ALL:

SELECT column0, column1, column3, column42
FROM   DT1
WHERE  column1 is null
OR     column1= '0'
OR     column1= 'example'
UNION ALL
SELECT column0, column1, column3, column42
FROM   DT2
WHERE  column42 like 'f%';

Which, for the sample data:

CREATE TABLE DT1 (Column0, Column1, column3, column42) AS
SELECT 1234,  'example', 345,  'final1' FROM DUAL UNION ALL
SELECT 1234,  'example', 567,  'final3' FROM DUAL UNION ALL
SELECT 1234,  'example', 789,  'final5' FROM DUAL UNION ALL
SELECT 12345, 'example', 7890, 'final9' FROM DUAL UNION ALL
SELECT 12345, 'example', 890,  'final10' FROM DUAL;

CREATE TABLE DT2 (Column0, Column1, column3, column42) AS
SELECT 4567,  'example', 345,  'final1' FROM DUAL UNION ALL
SELECT 4567,  'example', 567,  'final3' FROM DUAL UNION ALL
SELECT 4567,  'example', 789,  'final5' FROM DUAL UNION ALL
SELECT 45678, 'example', 7890, 'final9' FROM DUAL UNION ALL
SELECT 45678, 'example', 890,  'final10' FROM DUAL;

Outputs:

COLUMN0	COLUMN1	COLUMN3	COLUMN42
1234	example	345	final1
1234	example	567	final3
1234	example	789	final5
12345	example	7890	final9
12345	example	890	final10
4567	example	345	final1
4567	example	567	final3
4567	example	789	final5
45678	example	7890	final9
45678	example	890	final10

fiddle

edited Nov 14, 2022 at 10:24

answered Nov 14, 2022 at 9:33

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ahmad Over a year ago

still prints (0,0)

MT0 Over a year ago

@ahmad I am not entirely sure what point you are trying to make when you post code with no accompanying text; however your code is not valid as column3= ' 'tradeDate >= '{}' is not valid syntax (it looks like you are missing an AND or an OR). Even if you fix that I am unable to comment on whether it is correct or not as you have not provided a minimal reproducible example (with CREATE TABLE and INSERT statements for your sample data and details of the expected output) that we can test against.

ahmad Over a year ago

Hi the comment was posted accidentally was trying to go to an new line and posted it apologies the main code is in the question and the answer still prints (0,0)

MT0 Over a year ago

@ahmad It works for me if the WHERE clauses actually match the data fiddle. (But if the WHERE clauses do not match any rows then, of course, it will not return anything; as per the last example in the fiddle.)

ahmad Over a year ago

The where clause matches the data fiddle as if i run them in two separate queries and export them i will have about 200 rows for each but when i try to run both queries together i get (0,0).

|

Collectives™ on Stack Overflow

SQL how to join two tables with same column names but different data in SQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related