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.
Author |
Topic |
Ade
Starting Member
9 Posts |
Posted - 2013-06-03 : 08:59:34
|
HiI know this is simple but I just can't get my query to work. I am trying to select from two tables where I find a match on one field but have values for the second field only in one of the two tables.I've tried lots of different approaches and I just can't get it.--create sample datacreate table T1(client varchar(25),month int)create table T2(client varchar(25),month int)insert into T1 values('Bentley','1')insert into T1 values('Bentley','2')insert into T1 values('Bentley','4')insert into T1 values('Coventry','1')insert into T1 values('Coventry','3')insert into T1 values('Coventry','10')insert into T2 values('Coventry','1')insert into T2 values('Coventry','3')insert into T2 values('Coventry','4')insert into T2 values('Oxford','5')insert into T2 values('Luton','2')/*The following code does not return the desired result set.What I want is the row 'Coventry','4' to be returned. Because 'Coventry' is the client name found in both tables, but 'Coventry','4'is exclusive to T2.*/SELECT T2.Client, T2.monthFROM T2 WHERE EXISTS (SELECT * FROM T1 WHERE T2.client = T1.client AND T2.month <> T1.month)Thanks in advance for any help.RegardsAde |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-03 : 09:05:15
|
SELECT T2.Client,T2.monthFROM T2 JOIN T1 ON t1.client = t2.clientEXCEPTSELECT T1.Client,T1.monthFROM T2 JOIN T1 ON t1.client = t2.client--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-03 : 09:11:30
|
-- Alternate is:;WITH CTE AS ( SELECT T2.Client,T2.month FROM T2 JOIN T1 ON t1.client = t2.client ) SELECT * FROM CTE EXCEPT SELECT * FROM T1 --Chandu |
|
|
Ade
Starting Member
9 Posts |
Posted - 2013-06-03 : 09:41:47
|
Hi bandi.That's great, it works a treat. Thank you very much for the prompt and simple solution, much appreciated! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-03 : 09:46:06
|
quote: Originally posted by Ade Hi bandi.That's great, it works a treat. Thank you very much for the prompt and simple solution, much appreciated!
Welcome--Chandu |
|
|
|
|
|
|
|