merge tow querys in sql

Question

I have a issuse in sql query.

I have 2 select:

The first one showing:

 columnA|columnB
   A    |2
   B    |3
   D    |5

The other one showing:

columnA|columnC
     A   |1
     B   |5
     C   |7

I'm tying to merge this tow query,

this is my query:

with 
cte1 as (
select  A,B  from table1 group by A
),
cte2 as (
   select  A,C from table2  group by A
)
select c1.A, c1.B, c2.C
from cte1 c1 left join cte2 c2 
on c2.A = c1.A
order by c1.A asc

The problem is when I'm using "Left join" the result is :

ColumnA|ColumnB|ColumnC
A      |2      |1
B      |3      |5
D      |5      |null

It's not showing the value C for the second table,

And whene I'm using "right join" the result is :

ColumnA|ColumnB|ColumnC
A      |2      |1
B      |3      |5
C      |null   |7

It's not showing the value D for the first table,

The result should be like that:

 ColumnA|ColumnB|ColumnC
    A      |2      |1
    B      |3      |5
    C      |null   |7
    D      |5      |null

any solution please

This is invalid syntax: `select A,B from table1 group by A`. Your query will fail in SQL Server and almost any other database. — Gordon Linoff, Sep 04 '20 at 10:42
@GordonLinoff Except for MySQL, SQLite, and probably a couple of other edge cases. — Tim Biegeleisen, Sep 04 '20 at 10:44
@TimBiegeleisen . . . Happily, no longer true in MySQL. And for the other edge cases, `A` would need to be the primary key or unique, and aggregation would be superfluous. — Gordon Linoff, Sep 04 '20 at 13:08
@bfs . . . And I thought I explained why: the aggregation keys are not consistent with the unaggregated `select` columns. — Gordon Linoff, Sep 07 '20 at 15:09
@bfs . . . Your CTEs as written would fail in SQL Server. Period. The group by keys are inconsistent with the `select` columns. — Gordon Linoff, Sep 08 '20 at 02:36

score 0 · Answer 1 · answered Sep 04 '20 at 10:40

You are describing a full join:

with 
    cte1 as (...),
    cte2 as (...)
select coalesce(c1.a, c2.a) a, c1.b, c2.c
from cte1 c1
full join cte2 c2 on c2.a = c1.a

Note that the CTEs are not actually needed here, you can use subqueries instead:

select coalesce(c1.a, c2.a) a, c1.b, c2.c
from (...) c1
full join (...) c2 on c2.a = c1.a

score 0 · Accepted Answer · answered Sep 04 '20 at 10:40

0

Use full outer join

select coalesce(table1.ColumnA,table2.ColumnA) as ColumnA,
       ColumnB,ColumnC  
from table1 full outer join table2 on table1.ColumnA=table2.ColumnA

answered Sep 04 '20 at 10:40

Fahmi

37,315
5
22
31

1

yees thank you , i was looking for full outer join – bfs Sep 04 '20 at 10:47

score 0 · Answer 3 · answered Sep 04 '20 at 10:41

0

You seem to want a full outer join here:

SELECT
    COALESCE(t1.A, t2.A) AS A,
    t1.B,
    t2.C
FROM Table1 t1
FULL OUTER JOIN t2
    ON t1.A = t2.A;

Demo

answered Sep 04 '20 at 10:41

Tim Biegeleisen

502,043
27
286
360

merge tow querys in sql

3 Answers3

Demo