Help with Left Join

Question

Help with Left Join

Carlton Patterson 761

Hello Community,

Can someone help with a left join query

I would like to do a left join between table one

CREATE TABLE tableone (  
  
Id varchar(50),  
  
SinkCreatedOn datetime2,  
  
SinkModifiedOn datetime2,  
  
statecode int,  
  
statuscode int,  
  
ts_primarysecondaryfocus varchar(255),  
  
customertypecode varchar(255),  
  
address1_addresstypecode varchar(255),  
  
accountclassificationcode int,  
  
ts_easeofworking varchar(255),  
  
ts_ukrow varchar(255),  
  
preferredappointmenttimecode varchar(255),  
  
ts_address1addresstype int,  
  
xpd_relationshipstatus int,  
  
ts_relationship varchar(255))  
  
INSERT tableone VALUES  
  
('0bf6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,''),  
  
('0df6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,''),  
  
('0ff6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,''),  
  
('11f6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,''),  
  
('c4a24708-0989-e811-a95d-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),0,1,'','','',1,'','','',717750001,930580000,''),  
  
('c6a24708-0989-e811-a95d-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,''),  
  
('c8a24708-0989-e811-a95d-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,''),  
  
('caa24708-0989-e811-a95d-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,''),  
  
('cca24708-0989-e811-a95d-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,'')  
  
SELECT * FROM tableone

And table two

CREATE TABLE tabletwo (  
  
participationtypemask varchar(50),  
  
instancetypecode varchar(50),  
  
donotpostalmail varchar(50),  
  
donotfax varchar(50),  
  
donotphone varchar(50),  
  
ispartydeleted varchar(50),  
  
donotemail varchar(50),  
  
ts_primarysecondaryfocus varchar(50),  
  
customertypecode varchar(50),  
  
address1_addresstypecode varchar(50),  
  
accountclassificationcode varchar(50),  
  
ts_ukrow varchar(50),  
  
preferredappointmenttimecode varchar(50),  
  
address2_freighttermscode varchar(50))  
  
INSERT tabletwo VALUES  
  
('Sender','Not Recurring','Allow','Allow','Allow','No','Allow','Tier 1','Competitor','Bill To','Default Value','UK','Morning','Default Value'),  
  
('To Recipient','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow','Yes','Do Not Allow','Tier 2','Consultant','Ship To',NULL,'RoW','Afternoon',NULL),  
  
('CC Recipient','Recurring Instance',NULL,NULL,NULL,NULL,NULL,'TBC','Customer','Primary',NULL,NULL,'Evening',NULL),  
  
('BCC Recipient','Recurring Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Investor','Other',NULL,NULL,NULL,NULL),  
  
('Required attendee','Recurring Future Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Partner',NULL,NULL,NULL,NULL,NULL),  
  
('Optional attendee',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Influencer',NULL,NULL,NULL,NULL,NULL),  
  
('Organizer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Press',NULL,NULL,NULL,NULL,NULL),  
  
('Regarding',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Prospect',NULL,NULL,NULL,NULL,NULL)  
  
SELECT * FROM tabletwo

I would like to do the left join on fields:

ts_primarysecondaryfocus

ts_ukrow

The expected output should look something like the following:

Someone suggested the following, but it didn't work

SELECT a.<field>, b.<field>...  
  
FROM Table1 a  
  
LEFT JOIN Table1 b  
  
ON a.PK = b.FK  
  
WHERE b.OtherColumn IS NULL

Any help greatly appreciated

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Hi @Carlton Patterson
How about add a Row_number column before you Left Join the two tables. Check this:

;WITH CTE1 AS  
(  
 SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM tableone  
),CTE2 AS  
(  
 SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM tabletwo  
)  
SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode  
      ,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus  
	  ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking   
	  ,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow   
	  ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship  
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum

Best regards,
LiHong

Answer 2

Erland Sommarskog 128.9K MVP Volunteer Moderator

Looks like an uphill battle to me. I can't see anything in common between these tables that would permit a join. You say that you want to join on the columns ts_primarysecondaryfocus and ts_ukrow. But in tableone, these columns are blank throughout and in tabletwo a few rows have value, and the rest are NULL.

I saw that you posted an image of an expected result, but there is no description of why you want that result.

Carlton Patterson 761 Reputation points

2022-06-28T22:19:39.46+00:00

Hi Erland,

Thanks for getting in touch.

I’m trying to find the values in tabletwo that aren’t in tableone. So, in tabletwo you will notice values Tier1, Tier2, & TBC in field ts_primarysecondaryfocus. These values are missing from tableone in field ts_primarysecondaryfocus, and I would like them added to tabletwo.

Does that make sense?
Carlton Patterson 761 Reputation points

2022-06-29T07:48:04.25+00:00

Hi Erland,

Is there no way of establishing a field with a surrogate key (I'm sure I'm not stating that correctly, but I hope you know what I mean), so as to have a column to join on?
LiHong-MSFT 10,061 Reputation points

2022-06-29T08:00:22.707+00:00

Hi @Carlton Patterson
As Erland said, how could you Left join tableone and tabletwo when the ts_primarysecondaryfocus field in tableone is all blank?

I’m trying to find the values in tabletwo that aren’t in tableone.
and I would like them added to tabletwo

It doesn't make any sense from my opinion.
Does the ts_primarysecondaryfocus field have nothing to do with other fields in tableone?
Carlton Patterson 761 Reputation points

2022-06-29T08:24:20.477+00:00

Hi LiHong,

Thanks for getting in touch.

I sorry that I'm not making myself clear.

Basically, this is an exercise in finding out values that appear in one table but aren't appearing in another table i.e. which is why I'm suggesting either left or right join.

Ok, lets focus on one field 'ts_primarysecondaryfocus'.

In table one, it is missing values Tier1, Tier2, and TBC. However, these values appear in field 'ts_primarysecondaryfocus' in table two. I would like those values (which are missing in table one) to appear in table one.

Please let me know if that makes anymore sense.?

TBH, I think the real challenge is that fact that we have no Primary or Foreign Key to join on.
Carlton Patterson 761 Reputation points

2022-06-29T08:34:54.487+00:00

Hi LiHong,

They say a picture speaks a thousand words.

So this table one (it current has NULL values for 'ts_primarysecondaryfocus')

This is table two (which has the values that are missing from tableone i.e Tier 1, Tier 2, and TBC)

After doing either a left join or right join on 'ts_primarysecondaryfocus', I would like to see the following

Does that make more sense?
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2022-06-29T21:20:03.23+00:00

Not really. Why do you want them to appear on the lines with the GUIDs starting on zero, and why you want Tier 1 appear with the GUID 0bf6d52f-707f-e811-a95c-00224800cc97 and no other?

All you are saying is you want the values are in tabletwo to be in tableone, but that is not enough information.

Now, I noticed your other post about pivoting, and I got the impression that you were combining values into rows fairly arbitrarily. So maybe your answer is "it does not matter on which rows they end, as long as they get into the table".

But that's a very odd thing to ask for. Normally a row in a table describes a specific instance of a certain entity. It is not a random collection of values. About the only situation I can think of where you actually may want something like that is when building dummy data for testing.

Relational databases are designed to work with that model I describe. If you deviate from that pattern, things get difficult. Not only in writing queries, but also in communication.

As for a query, I think that Li's query below can serve you, but I was not keen on writing something like that, because it does not really make much sense. Unless you are really building some sort of test data set.

Share via

Help with Left Join

1 additional answer

Your answer