Join help in SQL

  • A+
Category:Languages

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

Table #1:

Join help in SQL

Table #2:

Join help in SQL

Desired output:

Join help in SQL

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?!

 


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 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: