I have a question about joining two tables using TOAD and SQL SERV data

Toad Data Point
1

Hi.

Data is on SQL SERVER:

I have two files that were originally one file divided into two by virtue of arelationship or lack of relationship of one variable to other variables in thesame record, a comparison.

The original file was duplicated by unique ID in many instances by virtue ofmultiple relationships.

File one is relationship <> to the other comparison variables in theoriginal file

File two is relationship = to other comparison variables in the original file

There are some of same Unique Ids in both files due to the duplicationmentioned above in original file.

I am looking to identify the unique IDs in the data that are relationship<> to other variables and that have no cooresponding record by unique idin the to other comparison variables file.

The original file had 3741 records.

File one (<>) has 1466 records
File two (=) has 2275 records

I have tried several LEFT JOINS, but not sure this is correct, becausedepending on how & what I join on I get less than 3,741 records returned or4 to 5K records returned.

My assumtion is that if this is done correctly I will get 3741 records and thepresence absence of some fields would indicate the records I am looking for.

Examples:

SELECT *

INTO RiskAdjust_Temp.dbo.WHP_PROF_Final

FROM RiskAdjust_Temp.dbo.WHP_PROF_Unequal u1

LEFT JOIN RiskAdjust_Temp.dbo.WHP_PROF_equal e1
on u1.UnrelatedMEMBID = e1.RelatedMEMBID;
/and e1.RelatedProcCode = u1.UnrelatedProcCode;/

SELECT COUNT(*) AS NumberOf FROM RiskAdjust_Temp.dbo.WHP_PROF_Final;/4,662/

2
.
SELECT *

INTO RiskAdjust_Temp.dbo.WHP_PROF_Final

FROM RiskAdjust_Temp.dbo.WHP_PROF_Equal e1

LEFT JOIN RiskAdjust_Temp.dbo.WHP_PROF_Unequal u1
on e1.RelatedMEMBID = u1.UnrelatedMEMBID
and e1.RelatedProcCode = u1.UnrelatedProcCode;

SELECT COUNT(*) AS NumberOf FROM RiskAdjust_Temp.dbo.WHP_PROF_Final;/3,105/

SELECT *

INTO RiskAdjust_Temp.dbo.WHP_PROF_Final

FROM RiskAdjust_Temp.dbo.WHP_PROF_Equal e1

LEFT JOIN RiskAdjust_Temp.dbo.WHP_PROF_Unequal u1
on e1.RelatedMEMBID = u1.UnrelatedMEMBID;
/and e1.RelatedProcCode = u1.UnrelatedProcCode;/

SELECT COUNT(*) AS NumberOf FROM RiskAdjust_Temp.dbo.WHP_PROF_Final;/5,764/

List of field values in the join tables:

RelatedMEMBID
RelatedProcCode
RelatedProcCodeDesc
RelatedConCatCode
Related_to_CCs

UnrelatedMEMBID
UnrelatedProcCode
UnrelatedProcCodeDesc
UnrelatedConCatCode
Unrelated_to_CCs

Would appreciate any advice.

Thank you.

WHP

2

To simplify:

you have two tables
RiskAdjust_Temp.dbo.WHP_PROF_Unequal - I’ll call this table U (for unequal)
RiskAdjust_Temp.dbo.WHP_PROF_equal - I’ll call this table E (for equal)

both those tables have a column MembId and a column ProcCode, except that in the U table the column names start with Unrelated and in the E table the column names start with Related

You run three queries

select * from U left join E on u.MembId = e.MembId

select * from E left join U on e.MembId = U.MembId and e.ProcCode = u.ProcCode

select * from E left join U on e.MembId = u.MembId

These queries return three different value sets, as we should all expect, since they are not identical.

What result set are you trying to get?

For these kinds of questions, YOU SHOULD ALWAYS post sample data and expected result.

e.g. Sample data

Table U
UMembId UProcCode UProcCodeDesc
1 A Adescription
2 B Bdescription
3 C Cdescription

Table E
EMembId EProcCode EProcCodeDesc
1 X Xdescription
2 B Bdescription
3 D Ddescription

What should the output look like?
UMembId UProcCode UProcCodeDesc EMembId EProcCode EProcCodeDesc
(list rows you expect to see in the output)

3

Thank you jacquesrk.

Your answer is what I needed.

WHP