need to select between 3 tables with a preconditio

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)

need to select between 3 tables with a preconditio

Author

Topic

Asfakull
Starting Member

3 Posts

Posted - 2011-03-17 : 05:03:53

I have 2 tables -
Table-1
code price 1 price 2
A 1 2
B 2 3
C 4 5
A 2 3
Table 2
code price 1 price 2
A 1 2
B 4 3
C 4 5
A 4 3

the query need to select common codes between these 2 tables with different price pair,
result set: B 2 3
B 4 3
A 4 3
A 2 3

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-03-17 : 05:44:46

Best to post a query with the sample data in table variables or temp tables to it's easy to test. Here's two solutions:



--create sample tables
DECLARE @T1 TABLE (
	code char(1),
	price1 real,
	price2 real
)
INSERT INTO @T1
SELECT 'A',1,2 UNION ALL
SELECT 'B',2,3 UNION ALL
SELECT 'C',4,5 UNION ALL
SELECT 'A',2,3


DECLARE @T2 TABLE (
	code char(1),
	price1 real,
	price2 real
)

INSERT INTO @T2
SELECT 'A',1,2 UNION ALL
SELECT 'B',4,3 UNION ALL
SELECT 'C',4,5 UNION ALL
SELECT 'A',4,3

--solution 1
SELECT * FROM @T1
UNION ALL
SELECT * FROM @T2
EXCEPT
SELECT * FROM @T1
INTERSECT
SELECT * FROM @T2

--or solution 2


SELECT
	code,price1,price2
FROM
	(
		SELECT * FROM @T1
		UNION ALL
		SELECT * FROM @T2
	) t
GROUP BY code,price1,price2
HAVING COUNT(*)=1

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-03-17 : 06:02:02

Edit: solution 3 was wrong!

Asfakull
Starting Member

3 Posts

Posted - 2011-03-20 : 04:35:19

quote:
Originally posted by michael.appleton

Edit: solution 3 was wrong!

what are u referring to by solution 3 ?

Asfakull
Starting Member

3 Posts

Posted - 2011-03-20 : 04:41:10

quote:
Originally posted by Asfakull

quote:
Originally posted by michael.appleton

Edit: solution 3 was wrong!

what are u referring to by solution 3 ?

By the way Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources