Left JOIN Help Revisited

  • Hello Community,

    I just received excellent support from a member on this forum called Mark Cowne who helped me create a table from another table.

    I now what like to use the table in a Left Join operation.

    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 joins on

    ts_primarysecondaryfocus

    ts_ukrow

    Attachments:
    You must be logged in to view attached files.
  • TLDR.

    SELECT a.<field>, b.<field>...

    FROM Table1 a

    LEFT JOIN Table1 b

    ON a.PK = b.FK

    WHERE b.OtherColumn IS NULL

    ?

    But what question are you trying to answer?

  • pietlinden,

    I haven't tested your solution yet, but what do you mean 'what question are you trying to answer'?

    I'm trying to achieve the result shown in the image.

  • I have just realised that pietlinden solution won't work without some kind of surrogate key.

    Can anyone else take a look at this please

  • carlton 84646 wrote:

    what do you mean 'what question are you trying to answer'?

    I'm trying to achieve the result shown in the image.

    What is the end goal; I want to get the results because I want the results isn't the question you are trying to answer. As a different example, if someone asked you "Why did you drive to Birmingham?" the answer isn't "To go to Birmingham", it's something like "I went to see Elton John at Concert". We know you want the results (drove to Birmingham), but we want to know the why.

    Why or what needs the results you want? What are you going to do with those results? What question do those results answer?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • So, there say a picture speaks a thousand words, so hopefully, the folloiwng illustration will further explain what I'm trying to accomplished

    Lets focus on field 'ts_primarysecondaryfocus'

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

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

    After doing either left or right join we have table three (afterjoin)

    Does this help

    Attachments:
    You must be logged in to view attached files.
  • Hi Thom,

    I hope my last update (with images) helps explain the endgame.

  • There appears to be no relationship between the tables.

    Are you relying on the first row joining to the first row and the second row joining to the second row? Unless you add those row numbers there isn't a way to do this, because there is nothing in either table that naturally has the same order the rows are in the table.

    If you add a rownum column to both table create scripts, then you can join on rownum, but I assume you have real source data that does not have the rownum, so it may work for this exercise, but probably doesn't solve the real problem.

    CREATE TABLE tableone (
    rownum int identity(1,1),
    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,'')

    CREATE TABLE tabletwo (
    rownum int identity(1,1),
    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 Id, SinkCreatedOn, SinkModifiedOn, statecode, statuscode, b.ts_primarysecondaryfocus
    FROM tableone AS a
    JOIN tabletwo AS b ON a.rownum = b.rownum

    • This reply was modified 1 month, 2 weeks ago by  Ed B.
    • This reply was modified 1 month, 2 weeks ago by  Ed B.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply