July 20, 2009 at 2:48 pm
I have 2 tables I'm trying to join. I need to run a query to display every vendor(t1.vendorid) and vendor address code (t1.vac1) in table1 that is not equal to the vendor(t2.vendorid) and vendor address code(t2.vac2) up in table2. For some reason I keep getting duplication.
sorry I'm new at this.
Thank you,
July 20, 2009 at 2:51 pm
Is this what you are looking for?
Select t1.vendorid,t1.vac1 from table1
Except
Select t2.vendorid,t2.vac2 from table2
July 20, 2009 at 3:30 pm
Thanks you its working so far.
July 21, 2009 at 12:36 am
You can get same performance with :
SELECT t1.vendorid,t1.vac1 from table1
WHERE t1.vendorid not in (SELECT t2.vendorid from table2)
AND t1.vac1 not in (SELECT t2.vac2 from table2)
July 21, 2009 at 8:28 am
Thanks, I guess its always two ways to skin a cat.
July 21, 2009 at 9:06 am
Here is a third way to skin a cat:
select
t1.vendorid,
t1.vac1
from
table1 t1
left outer join table2 t2
on (t1.vendorid = t2.vendorid
and t1.vac1 = t2.vac2)
where
t2.vendorid is null;
July 21, 2009 at 3:34 pm
It depends on what you want exactly. Of the three versions listed you will get two different results sets back..DECLARE @Table1 TABLE (VendorID INT, Vac1 INT)
INSERT @Table1
SELECT 1, 1
UNION ALL SELECT 1, 2
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 7
UNION ALL SELECT 4, 9
DECLARE @Table2 TABLE (VendorID INT, Vac2 INT)
INSERT @Table2
SELECT 1, 1
UNION ALL SELECT 1, 2
UNION ALL SELECT 2, 5
UNION ALL SELECT 3, 7
--Wesley's Version
Select t1.vendorid,t1.vac1 from @table1 AS t1
Except
Select t2.vendorid,t2.vac2 from @table2 AS t2
-- RESULTS
--vendorid vac1
------------- -----------
--2 3
--4 9
--(2 row(s) affected)
--@SQL's version
SELECT t1.vendorid,t1.vac1 from @table1 AS t1
WHERE t1.vendorid not in (SELECT t2.vendorid from @table2 AS t2)
AND t1.vac1 not in (SELECT t2.vac2 from @table2 AS t2)
-- RESULTS
--vendorid vac1
------------- -----------
--4 9
--(1 row(s) affected)
--Lynn's version
select
t1.vendorid,
t1.vac1
from
@table1 t1
left outer join @table2 t2
on (t1.vendorid = t2.vendorid
and t1.vac1 = t2.vac2)
where
t2.vendorid is null
-- RESUTLS
--vendorid vac1
------------- -----------
--2 3
--4 9
--(2 row(s) affected)
July 21, 2009 at 3:54 pm
The problem with @SQL's version is that the AND in the WHERE clause should be an OR. Change it and check out the results.
July 22, 2009 at 9:41 am
Lynn,
You are correct the Or in the SQL verison made a big difference in the result.
Thanks all for your help I really appreciate it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply