December 18, 2007 at 12:46 pm
Hi all.
I have two tables where I need to show only the rows which do not have a match in the other table. This shows the match. I selected it into a table for backup:
select
Segment , OWNbr , Name, TypeCode , Code, Gate, StartDate, EndDate,
Title, PGC, TermEmploy, InPosition,
TypeGroup, PAT, RS,
ModUser, ModDate, TIP
into CEM_smcsAssignmentsBU
from CEM_smcsAssignments a, CEM_AssignExtr b
where a.OWNbr = b.OWID
and a.TypeCode = b.[TYPE CODE]
and a.Code = b.[ASSIGNMENT CODE]
and a.Gate = b.GATE
and a.StartDate = b.[ASSIGNMENT START DATE]
and a.EndDate = [ASSIGNMENT END DATE]
I tried selecting this data, then deleting it, thinking only the discrepancies would be left... it did not go well. Used this delete statement.
Delete Segment , OWNbr , Name, TypeCode , Code, Gate, StartDate, EndDate,
Title, PGC, TermEmploy, InPosition,
TypeGroup, PAT, RS,
ModUser, ModDate, TIP
from CEM_smcsAssignments a, CEM_AssignExtr b
where a.OWNbr = b.OWID
and a.TypeCode = b.[TYPE CODE]
and a.Code = b.[ASSIGNMENT CODE]
and a.Gate = b.GATE
and a.StartDate = b.[ASSIGNMENT START DATE]
and a.EndDate = [ASSIGNMENT END DATE]
Also tried these to select just the rows without matches, from each table. Also didn't work. Retrieved many, many, many rows that I do not need. Any advise would be appreciated. tia
--Get data in smcs only
select a.*
--into CEM_smcsAssignmentsOnlyInSMCS
fromCEM.CEM_smcsAssignments a
left join CEM.CEM_AssignExtr b
on a.OWNbr = b.OWID
and a.Code = b.[ASSIGNMENT CODE]
and a.StartDate = b.[ASSIGNMENT START DATE]
and b.OWN is NULL
--Get data in STM only
select b.*
--into CEM_smcsAssignmentsOnlyInSTM
fromCEM.CEM_AssignExtr b
left join CEM.CEM_smcsAssignments a
on b.OWID = a.OWNbr
and b.[ASSIGNMENT CODE] = a.Code
and b.[ASSIGNMENT START DATE] = a.StartDate
and a.OWNbr is NULL
December 18, 2007 at 1:33 pm
Try a FULL OUTER JOIN:
DECLARE @Table1 TABLE (Col1 int)
DECLARE @Table2 TABLE (Col1 int)
INSERT INTO @Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
INSERT INTO @Table2
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10
SELECT t1.Col1, t2.Col1
FROM @Table1 t1
FULL JOIN @Table2 t2
ON t1.Col1 = t2.Col1
WHERE t1.Col1 IS NULL OR t2.Col1 IS NULL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply