June 11, 2010 at 7:50 am
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
June 11, 2010 at 8:27 am
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;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply