Help with a join query consisting two tables !!!

• zeeshanmbutt

  Newbie

  Points: 7

  More actions

  June 11, 2010 at 7:50 am

  #222646

  Hi All,

  I have a table Toman_Aggregate with the following fields and example records.

  Toman_Aggregate

  AggID, ProductID

  A0801193677,000801193526

  A0801193677,000801193527

  A0801193677,000801193529

  A0801193677,000801193531

  A0801193677,000801193539

  A0801193677,000801193672

  A0801193677,000801193677

  A0801193677,000801199812

  I have another table called Model_Aggregate with the following fields and example records.

  Model_Aggregate

  AggID, ProductID

  A0031949169,003194916912

  A0031949169,003194916913

  A0031949169,003194916915

  A0031949169,003194916917

  A0801193677,000801193526

  A0801193677,000801193527

  A0801193677,000801193529

  A0801193677,000801193531

  A0801193677,000801193539

  A0801193677,000801193672

  A0801193677,000801193677

  A0801193677,000801199812

  Using SELECT query I want to get all the records from Model_Aggregate table where the AggID is same in both tables but ProductID is different. In the above example the SELECT Query should return the following record only.

  --Result that I am looking for

  AggID, ProductID

  A0801193677,000666666666

  How can I do that in a SELECT query? Please help.

  The following script will create the above table with above records.

  --START OF SCRIPT

  CREATE TABLE [dbo].[#Model_Aggregate]

  (

  [AggID] [nvarchar](50) NULL,

  [ProductID] [nvarchar](50) NULL

  )

  CREATE TABLE [dbo].[#Toman_Aggregate]

  (

  [AggID] [nvarchar](50) NULL,

  [ProductID] [nvarchar](50) NULL

  )

  --Insertion into Two tables

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0031949169','003194916912')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0031949169','003194916913')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0031949169','003194916915')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0031949169','003194916917')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801193526')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801193527')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801193529')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801193531')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801193539')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801193672')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801193677')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000801199812')

  insert into dbo.#Model_Aggregate (AggID,ProductID) Values ('A0801193677','000666666666')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801193526')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801193527')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801193529')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801193531')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801193539')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801193672')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801193677')

  insert into dbo.#Toman_Aggregate (AggID,ProductID) Values ('A0801193677','000801199812')

  --END OF SCRIPT

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 11, 2010 at 8:27 am

  #1179220

  Try this:

  select

  ma.*

  from

  #Model_Aggregate ma

  left outer join #Toman_Aggregate ta

  on (ma.AggID = ta.AggID

  and ma.ProductID = ta.ProductID)

  where

  exists(select 1 from #Toman_Aggregate ta1 where ma.AggID = ta1.AggID) and

  ta.ProductID is null;