Help with a join query consisting two tables !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a join query consisting two tables !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-06-09 : 20:36:26

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
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 not return anything. Although my Query below get all 8 records which is wrong.

--My wrong Query below is returning 8 records which is wrong. No record should be return.
select distinct m.aggregateid, m.clientproductid
from Model_Aggregate m
join Toman_Aggregate t
on (m.aggid=t.aggid and m.productid<>t.productid)

Can someone tell me what is wrong with my query. Please suggest a correct query.

Thanks,

Zee

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-09 : 21:56:40

[code]
select *
from Model_Aggregate m
where not exists
(
select *
from Toman_Aggregate x
where x.aggid = m.aggid
and x.productid = m.productid
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-06-11 : 09:26:00

Khtan,

Your query is even getting records where the Aggid is same and also the Productid is same. Where as I want to get all the records from Model_Aggregate table where the AggID is same in both tables but ProductID is different.

Lets do it clearly. Run the following script to temporarliy create these two tables and then insert the 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

Now lets run your query

--Query
select *
from #Model_Aggregate m
where not exists
(
select *
from #Toman_Aggregate x
where x.aggid = m.aggid
and x.productid = m.productid
)

--Your query Output
AggID, ProductID
A0031949169,003194916912
A0031949169,003194916913
A0031949169,003194916915
A0031949169,003194916917
A0801193677,000666666666

In reality the query should only return the following result. Since this the only record from #Model_Aggregate table where "the AggID is also in #Toman_Aggregate but the ProductID is differnt".

--Result that I am looking for
AggID, ProductID
A0801193677,000666666666

How can we do that in the SELECT query?

Please suggest.

Thanks,

Zee

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-06-11 : 09:40:45

Can someone please help.

Thanks a lot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 09:42:41

select *
from #Model_Aggregate m
where not exists
(
select *
from #Toman_Aggregate x
where x.aggid = m.aggid
and x.productid = m.productid
)
AND EXISTS
(
select *
from #Toman_Aggregate x
where x.aggid = m.aggid
)

No, you're never too old to Yak'n'Roll if you're too young to die.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-06-22 : 17:10:26

Thanks webfred. It worked :)...thanks a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-23 : 03:19:53

welcome