how to write query to show in desired table fomat

• suresh0534

  SSCommitted

  Points: 1966

  More actions

  June 27, 2013 at 3:58 pm

  #279625

  Hi,

  i have a requirement in sql server 2005 i have 2 tables as follows in both tables column "name" as common

  Example :Below is table A

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

  name JANclient

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

  Srinivas ABC

  suraj XYZ

  Srinivas KKKK

  Raj SSSS

  Example:Table B

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

  name FEBclient

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

  Srinivas AAAA

  suraj ZZZZ

  mahesh PPP

  now i want the result both tables A and B as shown like below

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

  name JANclient FEBclient

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

  Srinivas ABC AAAA

  Srinivas KKK NULL

  suraj XYZ ZZZZ

  mahesh NULL PPP

  Raj SSSS NULL

  please help me

  Thanks

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  June 27, 2013 at 5:15 pm

  #1628168

  How do you define that you want to Join Srinivas AAAA with Srinivas ABC but not with Srinivas KKKK?

  Could you provide DDL and sample data in a consumable format? That way we can focus on the solution and not on preparing the data to work on it.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  June 28, 2013 at 9:25 am

  #1628328

  you need to use FULL OUTER JOIN

  SELECT ISNULL(t1.name, t2.name) AS name

  ,t1.JANclient

  ,t2.FRBClient

  FROM Table1 AS t1

  FULL OUTER JOIN Table2 AS t2 ON t2.name = t1.name

  However, as "name" is highly unlikely to be unique, you are going to have screwed results.

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]