Left join or not Left join ?

• Lou

  SSCrazy

  Points: 2929

  More actions

  April 28, 2014 at 8:14 am

  #286318

  Hello,

  I'm requesting your help for any ideas that could help me to do a better query, I'm trying to match table01 with a master table to find socnbr records that does not found in master and show only this records,

  I'm attaching my query and data structures, I'm not sure if this query could help me.

  select a.account, a.socnbr1,a.socnbr2, b.account, b.socnbr_master,b.socnbr_master1,

  b.socnbr_master2,b.socnbr_master3,b.socnbr_master4,b.socnbr_master5,b.socnbr_master6, b.socnbr_master7,b.socnbr_master8

  from dbo.table01 as a

  left join dbo.matertable as b on a.account=b.account and

  a.socnbr1 = b.socnbr_master

  or a.socnbr1 = b.socnbr_master1

  or a.socnbr1 = b.socnbr_master2

  or a.socnbr1 = b.socnbr_master3

  or a.socnbr1 = b.socnbr_master4

  or a.socnbr1 = b.socnbr_master5

  or a.socnbr1 = b.socnbr_master7

  or a.socnbr1 = b.socnbr_master8

  and a.socnbr2 = b.socnbr_master

  or a.socnbr2 = b.socnbr_master1

  or a.socnbr2 = b.socnbr_master2

  or a.socnbr2 = b.socnbr_master3

  or a.socnbr2 = b.socnbr_master4

  or a.socnbr2 = b.socnbr_master5

  or a.socnbr2 = b.socnbr_master6

  or a.socnbr2 = b.socnbr_master7

  or a.socnbr2 = b.socnbr_master8

  order by b.account

  CREATE TABLE [dbo].[table01](

  [Account] [nvarchar](255) NULL,

  [socnbr1] [nvarchar](255) NULL,

  [socnbr2] [nvarchar](255) NULL,

  [socnbr3] [nvarchar](255) NULL,

  [socnbr4] [nvarchar](255) NULL,

  [socnbr5] [nvarchar](255) NULL,

  [socnbr6] [nvarchar](255) NULL,

  [socnbr7] [nvarchar](255) NULL,

  [socnbr8] [nvarchar](255) NULL,

  [socnbr9] [nvarchar](255) NULL

  ) ON [PRIMARY]

  insert into table01

  select '1','1234567','7654321','5555555',null,null,null,null,null,null

  union

  select '2','1234568','7654321',null,null,null,null,null,null,null

  union

  select '3','1234566',null,null,null,null,null,null,null,null

  union

  select '4','1234566','11111111','22222222','44444444','33333333','66','77','384','178'

  union

  select '5','123','111','222','444','333','6','7',null,null

  CREATE TABLE [dbo].[Matertable](

  [agency] [char](5) NOT NULL,

  [account] [varchar](20) NULL,

  [socnbr_master] [varchar](10) NOT NULL,

  [Name] [varchar](600) NULL,

  [socnbr_master1] [char](10) NULL,

  [Name1] [varchar](80) NULL,

  [socnbr_master2] [char](10) NULL,

  [Name2] [varchar](80) NULL,

  [socnbr_master3] [char](10) NULL,

  [Name3] [varchar](80) NULL,

  [socnbr_master4] [char](10) NULL,

  [Name4] [varchar](80) NULL,

  [socnbr_master5] [char](10) NULL,

  [Name5] [varchar](80) NULL,

  [socnbr_master6] [char](10) NULL,

  [Name6] [varchar](80) NULL,

  [socnbr_master7] [char](10) NULL,

  [Name7] [varchar](80) NULL,

  [socnbr_master8] [char](10) NULL,

  [Name8] [varchar](80) NULL

  )

  ON [PRIMARY]

  GO

  insert into [Matertable]

  select '0300','1','1234567','Lou','7654321','Fred','5555555','Greg',null,null,null,null,null,null,null,null,null,null,null,null

  union

  select '0400','2','1234568','Mary','7654321','Don',null,null,null,null,null,null,null,null,null,null,null,null,null,null

  union

  select '0500','3','1234566','John','17','Phil',null,null,null,null,null,null,null,null,null,null,null,null,null,null

  union

  select '0600','4','1234566','Roy','11111111','Claire','22222222','Patterson','44444444','Mark','33333333',null,null,null,null,null,null,null,null,null

  union

  select '0700','5','123','Linda','111','Rose','222','Marge','444','Ann','333','Lis','6',null,null,null,null,null,null,null

  Thanks a lot for your help

• SQLCJ

  SSCommitted

  Points: 1791

  More actions

  April 28, 2014 at 8:19 am

  #1709185

  EXCEPT operator to find non existing records ? :unsure:

• Lou

  SSCrazy

  Points: 2929

  More actions

  April 28, 2014 at 9:00 am

  #1709205

  Ok,

  Good try, but suppose, that I have no order in the number that i want to match, so is difficult to determine if field1 in table01 is exactly related to field1 in matertable,so I will need to match with other fields

  insert into table01

  select '6','5','654','125',null,null,null,null,null,null

  union

  select '7','1069','126','1968',null,null,null,null,null,null

  insert into [Matertable]

  select '0300','6','125','Al','654','FredJr','5','Greg',null,null,null,null,null,null,null,null,null,null,null,null

  union

  select '0300','7','126','Ralph','1069','Astrid','1968','Mary Jo',null,null,null,null,null,null,null,null,null,null,null,null

  Thanks for your patience