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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy