To get a query

• su_kumar11

  SSCommitted

  Points: 1675

  More actions

  June 25, 2009 at 11:30 pm

  #211379

  Hi,

  I have two table as below....

  Table A

  Id Name

  1 Sunil

  2 Anil

  3 Amit

  4 RAj

  Table B

  Id Add

  1 Mumbai

  2 Pune

  I want the result from these two tables as below:

  Id Name

  3 Amit

  4 Raj

  Without using subquery and any hardcode.

  Please help me to get the result.

  Thanks,

  Sunil

• arun.sas

  SSChampion

  Points: 11831

  More actions

  June 26, 2009 at 12:58 am

  #1015800

  Hi,

  Try this

  create table #TableA

  (

  ID int,

  Name varchar(10)

  )

  insert into #TableA

  select 1,'Sunil'

  union all

  select 2,'Anil'

  union all

  select 3,'Amit'

  union all

  select 4,'Raj'

  create table #TableB

  (

  ID int,

  Address Varchar(20)

  )

  insert into #TableB

  select 1,'Mumbai'

  union all

  select 2,'pune'

  select a.ID,a.Name

  from #TableA a ,

  #TableB b

  where a.ID b.ID

  group by a.ID,a.Name

  having count(*) = 2

  ARUN SAS

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  June 26, 2009 at 1:50 am

  #1015820

  Do you want to see the IDs in table 1 that aren't in table2?

  If so, this will work

  -- same sample tables as ARUN SAS created

  select a.ID,a.Name

  from #TableA a LEFT OUTER JOIN #TableB b ON a.ID = b.ID

  WHERE #TableB.ID IS NULL

  Why no subqueries?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass