September 24, 2015 at 9:19 am
Hi
I am using the following views from two db's to find records that don't match.
My question is can I have output of fields from the second table
SELECT distinct T1.[last name],t1.[first name],ENum
FROM ECLINICIAN_Info T1
WHERE NOT EXISTS(SELECT *
FROM ACLINICIAN_Info T2
WHERE t1.Enum = t2.Anum
and t1.[last name] = t2.lname and t1.[first name] = t2.Fname)
I would like to include t2.Anum in my select
Thanks
Joe
September 24, 2015 at 10:41 am
well if it doesn't exist, then you'd be adding a column of all null values right?
you can join the two tables together instead of an exists, but that finds things that are the same.
SELECT distinct T1.[last name],t1.[first name],ENum,T2.lname ,t2.Fname,
FROM ECLINICIAN_Info T1
LEFT JOIN ACLINICIAN_Info T2
ON t1.Enum = t2.Anum
and t1.[last name] = t2.lname
and t1.[first name] = t2.Fname)
Lowell
September 24, 2015 at 10:47 am
Thanks for getting back
True, and I didn't really explain it right, sometimes the ENum and ANum are there but don't match and would like to select the Anum so the output would show both
September 24, 2015 at 10:52 am
maybe join only on names,and filter for where the ENum <> Anum?
SELECT distinct T1.[last name],t1.[first name],ENum,t2.Anum,T2.lname ,t2.Fname,
FROM ECLINICIAN_Info T1
LEFT JOIN ACLINICIAN_Info T2
ON t1.[last name] = t2.lname
and t1.[first name] = t2.Fname
WHERE t1.Enum <> t2.Anum
Lowell
September 24, 2015 at 11:07 am
Great I think this will work after I do some data checking
added to the where to also show null values
SELECT distinct T1.[last name],t1.[first name],ENum,t2.Anum,T2.lname ,t2.Fname,
FROM ECLINICIAN_Info T1
LEFT JOIN ACLINICIAN_Info T2
ON t1.[last name] = t2.lname
and t1.[first name] = t2.Fname
WHERE t1.Enum <> t2.Anum or (t1.Enum isnull) or ( t2.Anum is null)
September 24, 2015 at 11:49 am
If you're looking for distinct members in one that don't exist in the other, or possibly members that exist between the two, then consider the EXCEPT and INTERSECT operations.
https://msdn.microsoft.com/en-us/library/ms188055.aspx
-- selects distinct members from T1 that don't exist in T2.
SELECT [last name],t1.[first name], Enum FROM ECLINICIAN_Info as T1
EXCEPT
SELECT lname, Fname, Anum FROM ACLINICIAN_Info as T2;
-- selects distinct members from T2 that don't exist in T1.
SELECT lname, Fname, Anum FROM ACLINICIAN_Info as T2
EXCEPT
SELECT [last name],t1.[first name], Enum FROM ECLINICIAN_Info as T1;
-- selects distinct members from T1 that do exist in T2.
SELECT [last name],t1.[first name], Enum FROM ECLINICIAN_Info as T1
INTERSECT
SELECT lname, Fname, Anum FROM ACLINICIAN_Info as T2;
Unless you're planning to also include non-joining columns in addition to name and ID, I don't see the point in using an OUTER JOIN for this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply