March 21, 2013 at 5:56 pm
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
)
March 21, 2013 at 9:11 pm
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
March 21, 2013 at 9:41 pm
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);
March 22, 2013 at 4:30 am
Chrissy321 (3/21/2013)
SELECT * FROM @test1WHERE
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2013 at 8:11 am
Thank you all once again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply