CODEWITHSUNDEEP
sqlsql-serverjoinunion
Rick
Rick

Reputation: 1559

Join help in SQL

I have 2 very simple tables to join but I am missing somewhere badly that I am not getting the desired output:

Table #1:

enter image description here

Table #2:

enter image description here

Desired output:

enter image description here

Query:

create table #temp1
(
     client_id int, 
     identifier int, 
     pp_id int,
     ch_id int,
     code varchar(20),
     program varchar(20),
     startdate date,
     enddate date, 
     ssc varchar(50)
)

insert into #temp1
values (9908,789654123,1567,1566,'OP','xASMT','1/1/2019','1/4/2019','A201901044F010134NNN01D               151 143 093 ')

create table #temp2
(
     client_id int, 
     identifier int, 
     pp_id int,
     ch_id int,
     code varchar(20), 
     program varchar(20),
     startdate date,
     enddate date, 
     ssc varchar(20)
)

insert into #temp2
values(9908,789654123,1574,1573,'OP','SU1','1/1/2019','1/4/2019',NULL)

--My query:
select 
    t1.client_id, t1.identifier, 
    concat(t1.code, t1.startdate, t1.enddate, t1.ssc),
    concat(t2.code, t2.startdate, t2.enddate, t2.ssc)
from 
    #temp1 t1
left join 
    #temp2 t2 on t1.client_id = t2.client_id and t1.identifier = t2.identifier

I am still a learner and pardon me if there are any mistakes here. Any help?!

Upvotes: 3

Views: 168

Answers (5)

Caius Jard
Caius Jard

Reputation: 74605

Here's what you WOULDN'T do, just posting because you asked about JOIN. It's definitely the wrong way to go, but:

select 
  COALESCE(t1.client_id, t2.client_id) client_id,
  COALESCE(t1.identifier, t2.identifier) identifier,
  COALESCE(
    CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc),  
    concat(t2.code,t2.startdate,t2.enddate,t2.ssc)
  )
from 
  #temp1 t1
  full outer join 
  #temp2 t2 
  on 0 = 1

A full outer join between these tables on an impossible condition means you end up with a resultset like:

t1.client_id t2.client_id
9908         NULL
NULL         9908

The COALESCE brings the split-ness of it back together:

client_id
9908
9908

As noted, don't do it- it's a huge waste of the database's time and resources compared to union; I purely wrote it as an example of how vertical growth of a resultset can be achieved using JOINs and also as an aid to your understanding of db theory and operation:

A UNION B (number is id)

Results grow vertically:
A1
A2
B1
B2

A JOIN B (number is id)

Results grow horizontally:
A1 B1
A2 B2

Outer joins preserve the row from the table even if there is no match:

A OUTER JOIN B

Results:
A1   null
null B2

By making the join impossible, a full outer join will cause a resultset that grew horizontally and vertically:

A OUTER JOIN B ON 1 = 0

Results:
A1   null
A2   null
null B1
null B2

COALESCE returns the first non-null argument, so if we COALESCE(a_column, b_column) it collapses those two columns, one of which is null, into one column:

acol bcol  COALESCE(acol, bcol)  result
----|-----|--------------------|--------
A1   null  COALESCE(A1, null)   -> A1
null B2    COALESCE(null, B1)   -> B1

Upvotes: 5

Marc0
Marc0

Reputation: 191

This is what I would use to get that output:

select 
    t1.client_id, 
    t1.identifier, 
    CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc) ssc_concatenated
    --concat(t2.code,t2.startdate,t2.enddate,t2.ssc)

from #temp1 t1

union all

select 
    t2.client_id, 
    t2.identifier, 
    concat(t2.code,t2.startdate,t2.enddate,t2.ssc) ssc_concatenated
from #temp2 t2

Here's the db<>fiddle demo

Upvotes: 2

Zeki Gumus
Zeki Gumus

Reputation: 1484

Regarding your desired result I understand you need to use UNION ALL instead of join the tables.

Please check the script :

SELECT
    t1.client_id
   ,t1.identifier
   ,CONCAT(t1.code, t1.startdate, t1.enddate, t1.ssc)
   --,CONCAT(t2.code, t2.startdate, t2.enddate, t2.ssc)
FROM #temp1 t1
UNION ALL
SELECT 
    t2.client_id
   ,T2.identifier
   ,CONCAT(t2.code, t2.startdate, t2.enddate, t2.ssc)
FROM  #temp2 t2

Upvotes: 2

vittore
vittore

Reputation: 17579

What you seem to look for is a UNION select construct, not a join.

UNION returns rows from one sub-query and appends rows from another sub-query

So with your example

select t1.client_id, t1.identifier, CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc
from #temp1 t1 
where ... some condition ...
union all 
select t2.client_id, t2.identifier, concat(t2.code,t2.startdate,t2.enddate,t2.ssc)
from #temp2 t2 
where ... some condition ...

See docs here: UNION

Upvotes: 3

forpas
forpas

Reputation: 164069

I don't believe you need a join, but you need union:

select t1.client_id, t1.identifier, CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc)
from #temp1 t1
union all
select t2.client_id, t2.identifier, CONCAT(t2.code,t2.startdate,t2.enddate,t2.ssc)
from #temp2 t2

maybe you need a where part to restrict the result for certain rows.
See the demo

Upvotes: 6

Related Questions