can i use union to resolve this issue?

• dsandhyarao

  Say Hey Kid

  Points: 683

  More actions

  March 18, 2010 at 10:23 am

  #145954

  hi,

  I have 2 tables with few columns. i need to show the combination of both tables in a report.

  please advice

  table one

  ( firstname,lastname,processdate,empno,deadlinedate

  )

  table two

  ( lastname, empno,addresss1,state,reason1,type,submitdate,status,lettermailed)

  The values not there in table one should display N/A for columns of table2.

  the Result i need is like

  eno fname lname processdate deadlinedate addresss1 state reason1 type submitdate status lettermailed

  1 xyz abc 02/02/2010 02/28/2010 n/a n/a n/a n/a n/a n/a n/a

  2 dd dsd 02/02/2010 02/28/2010 mainstr IL term new 02/15/2010 active 02/12/2010

  if the same empno exists in 2 table then i need to show the combination of fileds from 2 tbales as 1 row, if the value exists only in 1 table, all teh other columns should have n/a.

  thanks

• Ray Dai

  SSCommitted

  Points: 1548

  More actions

  March 18, 2010 at 10:37 am

  #1136139

  Couldn't you just use a join?select

  O.EmpNo as Eno

  ISNULL(O.FirstName, 'N/A'),

  ISNULL(O.LastName, 'N/A'), --what if they have different last name?

  ISNULL(O.processdate, 'N/A'),

  ISNULL(O.deadlinedate, 'N/A'),

  ISNULL(T.addresss1 , 'N/A')

  ...

  FROM TableOne O INNER JOIN TableTwo T

  on O.EmpNo = T.EmpNo