Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NOT IN based on two fields Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 5:56 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 653, Visits: 3,840
Please help me to not use this silly concatenation method. I want to select all from the first table when the combination of two fields is not in the second.

What;s the accepted way to do this?

DECLARE @Test1 TABLE (Field1 varchar(1), Field2 int)
INSERT INTO @Test1
SELECT 'A',1 UNION
SELECT 'B',2 UNION
SELECT 'C',3

DECLARE @Test2 TABLE (Field1 varchar(1), Field2 int)
INSERT INTO @Test2
SELECT 'B',2 UNION
SELECT 'C',3


SELECT * FROM @Test1
WHERE
Field1 + CONVERT(varchar(1),Field2) NOT IN
(
SELECT Field1 + CONVERT(varchar(1),Field2) FROM @Test2
)
Post #1434093
Posted Thursday, March 21, 2013 9:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
I may be making it too simple and missing some nuance of what you are after, but I think you just want a LEFT JOIN.

SELECT  t1.Field1,
t1.Field2
FROM @Test1 t1
LEFT JOIN @Test2 t2 ON t1.Field1 = t2.Field1
AND t1.Field2 = t2.Field2
WHERE t2.Field1 IS NULL;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1434119
Posted Thursday, March 21, 2013 9:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 23,286, Visits: 32,011
This works also:


DECLARE @Test1 TABLE (Field1 varchar(1), Field2 int);
INSERT INTO @Test1
SELECT 'A',1 UNION
SELECT 'B',2 UNION
SELECT 'C',3 ;

DECLARE @Test2 TABLE (Field1 varchar(1), Field2 int);
INSERT INTO @Test2
SELECT 'B',2 UNION
SELECT 'C',3 ;

select * from @Test1 t1
where not exists(select 1 from @Test2 t2 where t1.Field1 = t2.Field1 and t1.Field2 = t2.Field2);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1434123
Posted Friday, March 22, 2013 4:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
Chrissy321 (3/21/2013)

SELECT * FROM @Test1
WHERE
Field1 + CONVERT(varchar(1),Field2) NOT IN
(
SELECT Field1 + CONVERT(varchar(1),Field2) FROM @Test2
)


SELECT * FROM @Test1 t2 
WHERE NOT EXISTS (SELECT 1 FROM @Test2 t2 WHERE t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2)




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1434187
Posted Friday, March 22, 2013 8:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 653, Visits: 3,840
Thank you all once again.
Post #1434290
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse