TSQL Query help required

• arun.sivaprakash

  Grasshopper

  Points: 11

  More actions

  January 12, 2009 at 7:09 am

  #127885

  create table #tbl1(

  col1 char(5) not null,

  col2 char(10) not null,

  col3 varchar(16) not null,

  col4 varchar(16) not null,

  col5 varchar(16) null )

  create table #tbl2(

  col1 char(5) not null,

  col2 char(10) not null,

  col3 varchar(16) not null,

  col4 varchar(16) not null,

  col6 char(5) not null)

  insert into #tbl1(col1,col2,col3,col4,col5)

  select 'NYNY1','1211000AA0','SWAP','%','Marlene Francis' UNION ALL

  select 'NYNY1','1211000BB0','SWAP','%','Marlene Francis' UNION ALL

  select 'NYNY1','1211000CC0','SWAP','%','Marlene Francis' UNION ALL

  select 'NYGCI','1211000AA0','FORWARD','%','Marlene Francis' UNION ALL

  select 'NYGCI','1211000BB0','FORWARD','%', 'Marlene Francis' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','%', 'Marlene Francis' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','B100','Robert Frangella' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','B257','Brian Kidd'

  insert into #tbl2(col1,col2,col3,col4,col6)

  select 'NYNY1','1211000AA0','SWAP', '100', 'N/A' UNION ALL

  select 'NYNY1','1211000BB0','SWAP', '100', 'N/A' UNION ALL

  select 'NYNY1','1211000CC0','SWAP','101','N/A' UNION ALL

  select 'NYGCI','1211000AA0','FORWARD','102', 'N/A' UNION ALL

  select 'NYGCI','1211000BB0','FORWARD','107', 'N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','110', 'N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','120', 'N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','180', 'N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','480', 'N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','B100', 'N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','B257','N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','B258','N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','B259','N/A' UNION ALL

  select 'NYGCI','1211000CC0','FORWARD','B260','N/A'

  Expected Result

  ---------------

  col1 col2col3col4 col5 col6

  -------- -------------------------- ---------------- -------- ----------------

  NYNY1 1211000AA0SWAP100N/A Marlene Francis

  NYNY1 1211000BB0SWAP100N/A Marlene Francis

  NYNY1 1211000CC0SWAP101N/A Marlene Francis

  NYGCI 1211000AA0FORWARD102N/A Marlene Francis

  NYGCI 1211000BB0FORWARD107N/A Marlene Francis

  NYGCI 1211000CC0FORWARD110N/A Marlene Francis

  NYGCI 1211000CC0FORWARD120N/A Marlene Francis

  NYGCI 1211000CC0FORWARD180N/A Marlene Francis

  NYGCI 1211000CC0FORWARD480N/A Marlene Francis

  NYGCI 1211000CC0FORWARDB100N/A Robert Frangella

  NYGCI 1211000CC0FORWARDB257N/A Brian Kidd

  NYGCI 1211000CC0FORWARDB258N/A Marlene Francis

  NYGCI 1211000CC0FORWARDB259N/A Marlene Francis

  NYGCI 1211000CC0FORWARDB260N/A Marlene Francis

  I need to create a simple query to join #tbl1 & #tbl2 for retrieving the below result. Can any one of you help me on this ?

  Note:

  -----

  1) For table #tbl1 & #tbl2. col1, col2, col3 constitutes the unique key

  2) On a close look at both the tables, col1, col2 & col3 are the same and col4 in the #tbl1 has similar values with col4 values in the #tbl2 but other values are nothing but

  wildcard '%', so in the resultset the wildcard caharact

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  January 12, 2009 at 7:21 am

  #925250

  What query have you tried so far, what result did it get you and what is wrong with it?

• Maxim Picard

  SSCrazy

  Points: 2052

  More actions

  January 12, 2009 at 7:53 am

  #925272

  Hello arun,

  Are you familiar with the CTE (common table expression) syntax as well as the Row_Number() function? Used together they work very well from requirements like yours:

  ;WITH tblRowNumbered

  as

  (

  Select

  Row_number() Over (

  partition by

  #tbl1.col1,#tbl1.col2,#tbl1.col3,#tbl2.col4

  order by

  #tbl1.col1,#tbl1.col4 desc) RowId

  ,#tbl1.col1

  ,#tbl1.col2

  ,#tbl1.col3

  ,#tbl2.col4

  ,#tbl2.col6 as col5

  ,#tbl1.col5 as col6

  from

  #tbl1 inner join #tbl2

  on #tbl1.col1 = #tbl2.col1

  and #tbl1.col2 = #tbl2.col2

  and #tbl1.col3 = #tbl2.col3

  and (#tbl1.col4 = #tbl2.col4

  or #tbl1.col4 = '%')

  )

  select col1,col2,col3,col4,col5,col6 from tblRowNumbered

  where RowId = 1

  Make sure to properly evaluate the query before running it on bigger dataset, with the temp tables and no index it's sure to do 2 full table scans.