Comparing 2 tables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Comparing 2 tables

Author

Topic

bjcc77
Starting Member

5 Posts

Posted - 2006-02-06 : 06:12:51

I have 2 tables Pricelist and Update , I am needing to compare Pricelist with Update to give me the new products that have been added to the update table then I need to compare Update with Pricelist giving me the products that are not in the Pricelist table giving me the retired products. The column "code" is the only common element as the other columns vary

Table : Pricelist

Code

Table : Update

Code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-06 : 06:20:35

PriceList not exists in Update

select * from [Pricelist] p where not exists (select * from [Update] u where u.Code = p.Code)

Update not exists in Pricelist

select * from [Update] u where not exists (select * from [PriceList] p where p.Code = u.Code)

----------------------------------
'KH'

bjcc77
Starting Member

5 Posts

Posted - 2006-02-06 : 06:27:14

Thanks

Kristen
Test

22859 Posts

Posted - 2006-02-06 : 08:34:30

Or a FULL OUTER JOIN:


SELECT
	[Source] = CASE WHEN P.Code IS NOT NULL THEN 'P' ELSE ' ' END
		+ CASE WHEN U.Code IS NOT NULL THEN 'U' ELSE ' ' END,
	*
FROM	Pricelist AS P
	FULL OUTER JOIN Update AS U
		ON U.Code = P.Code
-- If you only want ones in either P or U but not both then add:
WHERE	(U.Code IS NULL OR P.Code IS NULL)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources