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
 General SQL Server Forums
 New to SQL Server Programming
 select where exists query

Author  Topic 

Ade
Starting Member

9 Posts

Posted - 2013-06-03 : 08:59:34
Hi

I 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 data

create 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.month
FROM T2
WHERE EXISTS (SELECT * FROM T1 WHERE T2.client = T1.client AND T2.month <> T1.month)


Thanks in advance for any help.

Regards

Ade

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 09:05:15

SELECT T2.Client,T2.month
FROM T2
JOIN T1 ON t1.client = t2.client
EXCEPT
SELECT T1.Client,T1.month
FROM T2
JOIN T1 ON t1.client = t2.client


--
Chandu
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -