Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Andrzejek on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Inner Join on 3 tables resulting in Cartesian Product 1

Status
Not open for further replies.
SDS100UK

SDS100UK

MIS
Jul 15, 2001
185
GB
Hi,

I am fairly new to Oracle SQL and beg your patience with me.

I have 3 tables joined, I believe, appropriately, but clearly not as I get far too many results.

Here is the sql. . .

SELECT p.per_id,
p.per_first_names || ' ' || p.per_surname asFull_Name,
ass.asm_subject_id,
ah.ash_assessment_type
FROM o_persons p

INNER JOIN o_assessments ass
on p.per_id = ass.asm_subject_id
INNER JOIN o_assessment_header ah
on ah.ash_subject_id = p.per_id
WHERE ass.asm_context like '%ADULT%' and p.per_id = '520980'
ORDER BY p.per_id;

I have the Person table linked to the Assessments table and the assessment_header table is linked - so I cannot understand why I am getting 121 results when I should get 11.

Any help will be much appreciated.

Steven
 
Sort by date Sort by votes
It's tough to do the analysis without seeing at least a portion of the results. The first thing that strikes me is the LIKE in the where clause. That's a potential source of duplicates.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile big data clouds)


 
Upvote 0 Downvote
whenever you are getting what you consider duplicates the solution is always to query each individual table for the records of one of those duplicates - one or more of them is going to return more records than you expected and will highlight either that the returned data is correct, or that you need to further filter it down by extra criteria

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks, guys,

I know that I am getting duplicates because I know that client 520980 has only 11 assessments.

The results are:-
p.per_id Full_Name ass.asm_subject_id Assessment Type
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 B.I.C.A. - FACE v6
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 Blue Badge Assessment
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 BLUE BADGE ASSESSMENT
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 DECISION SUPPORT TOOL V3
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F01 URGENT AUTHORISATION NOTIFICATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION
520980 FIRST TEST 520980 F02 REQUEST FOR EXTENSION OF URGENT AUTHORISATION

I have tried the joins separately. When I use the joins for o_persons and o_assessments the results are fine.
Its when I use o-persons and o_assessment_header. It looks like there is no direct relationship between the 2 tables. So using inner joins how do I say "join o_persons to o_assessments and then o_assessments to o_assessment_header??

I hope this helps??

Steven
 
Upvote 0 Downvote
put here the full output of the following sql

select *
from o_persons
where per_id = '520980'
and rownum < 10;

select *
from o_assessments
where asm_subject_id = '520980'
and asm_context like '%ADULT%'
and rownum < 10;

select *
from o_assessment_header
where ash_subject_id = '520980'
and rownum < 10;


and also give us the primary key and unique indexes of each of the tables above


Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Hi Frederico,

Thank you for your time.

As requested. . .

select *
from o_persons
where per_id = '520980'
and rownum < 10;

returns. . .
PER_ID PER_SURNAME PER_BIRTH_DATE PER_BIRTH_DATE_EST_IND PER_CLIENT_IND PER_EMPLOYEE_IND PER_FILE_REF PER_FIRST_NAMES PER_GENDER PER_INITIALS PER_NAT_INS PER_OUN_SHORT_NAME PER_PAYROLL_NO PER_PRIORITY PER_TITLE PER_DECEASED_IND PER_DECEASED_DATE PER_FILE_LOCATION PER_WARN_IND PER_CONT_SERV_DATE PER_DOH_NUMBER PER_ACC_PAY_REF PER_ETHNICITY PER_NOTES PER_EXTERNAL_FLAG PER_DEBTOR_REF PER_EXTERNAL_CODE PER_CREATED_BY PER_CREATED_DATE PER_UPDATED_BY PER_UPDATED_DATE PER_DUPLICATE_FLAG PER_CP_ADULTHOOD_DATE PER_CPOINT_ID PER_NOT_CARRIED_TO_TERM_FLAG PER_NOT_CARRIED_TO_TERM_DATE PER_CASE_CLOSURE_DATE PER_CASE_CLOSURE_NOTES
520980 TEST 01-JAN-45 Y Y N FIRST M F MR APRIEST 05-NOV-10 RWEEKS 28-AUG-14




select *
from o_assessments
where asm_subject_id = '520980'
and asm_context like '%ADULT%'
and rownum < 10;
returns. . .
ASM_ID ASM_CONTEXT ASM_QSA_ID ASM_QSE_ID ASM_SUBJECT_IND ASM_SUBJECT_ID ASM_START_DATE ASM_END_DATE ASM_OUTCOME ASM_SCORE ASM_SHARE_DATA ASM_SHARE_NOTES ASM_COMPLETE_FLAG ASM_COMPLETE_DATETIME ASM_AUTH_BY ASM_AUTH_DATETIME ASM_CREATED_DATE ASM_CREATED_BY ASM_UPDATED_DATE ASM_UPDATED_BY ASM_LOCKED ASM_COMPLETED_BY ASM_CTC_OUTCOME ASM_PRIORITY ASM_PROVIS_PRIORITY ASM_ORIGINATING_ASM_ID ASM_CTC_COMPLETE_DATETIME ASM_EOF_CC ASM_AUTH_NOTES ASM_CHILD_SEEN ASM_CS_OVERRIDE ASM_CS_OR_REASON ASM_RAS_SCORE ASM_PERS_BUDGET ASM_SDS_STATUS ASM_GROSS_BUDGET ASM_BUDGET_CONTRIB ASM_ACTIONPLAN_YN ASM_DUPLICATED ASM_SDS_ASM_ID ASM_RAS_ADJUSTMENT ASM_RAS_INFORMAL_SUPP ASM_RAS_INFORMAL_SUPP_LEVEL ASM_RAS_INFORMAL_SUPP_CONCERN ASM_RAS_INFO ASM_SDS_USE_INDICATIVE_YN ASM_SDS_BUDGET ASM_SDS_CONTRIBUTION ASM_SDS_CHANGE_REASON ASM_SDS_NOTES ASM_SIG_YN ASM_PCP_END_REASON ASM_PCP_OUTCOME ASM_PCP_CONTEXT ASM_PCP_TYPE ASM_PCP_AIM ASM_AUTO_MANAGE_CPLANS_CP ASM_AUTO_MANAGE_CPLANS_NONCP ASM_NON_EVENT_OUTCOME_REASON
A1679059 ADULT AA2026 P 520980 01-DEC-14 01-JUN-15 A36HOO Y 02-JUN-15 01-DEC-14 RWEEKS 02-JUN-15 SSCOLLIN E3111 N N N N N N N
A1679064 ADULT AA2026 P 520980 01-DEC-14 01-JUN-15 A36CNFA Y 02-JUN-15 01-DEC-14 RWEEKS 02-JUN-15 SSCOLLIN E3111 A1679059 N N N Y N N N N
A1601861 ADULT AA2025 P 520980 28-AUG-14 01-DEC-14 A35SCOFF Y 01-DEC-14 28-AUG-14 RWEEKS 01-DEC-14 RWEEKS E5112 N N N N N N N
A1545747 ADULT A2045 P 520980 11-JUN-14 11-JUN-14 ABANDON N 11-JUN-14 11-JUN-14 FFKILBEY 11-JUN-14 FFKILBEY N N N N N N N
A893303 ADULT AA1145 P 520980 31-AUG-11 31-AUG-11 ABANDON Y 31-AUG-11 31-AUG-11 NGODDARD 31-AUG-11 NGODDARD E3864 N N N N
A1038363 XADULT AA1265 P 520980 17-MAY-12 17-MAY-12 ABANDON N 17-MAY-12 17-MAY-12 RALDRIDG 17-MAY-12 RALDRIDG N N N N N
A1097901 XADULT AA1085 P 520980 24-AUG-12 24-AUG-12 ABANDON N 24-AUG-12 24-AUG-12 OBALE 24-AUG-12 OBALE N N N N N
A1052093 ADULT AA1145 P 520980 12-JUN-12 16-JUN-12 ABANDON N 16-JUN-12 12-JUN-12 CBASS 16-JUN-12 CBASS N N N N N
A1119829 ADULT AA1145 P 520980 02-OCT-12 03-OCT-12 ABANDON N 03-OCT-12 02-OCT-12 EWILLIS 03-OCT-12 EWILLIS N N N N N




select *
from o_assessment_header
where ash_subject_id = '520980'
and rownum < 10;
returns. . .

ASH_ASM_ID ASH_SUBJECT_ID ASH_SUBJECT_IND ASH_SUBJECT_NAME ASH_SUBJECT_WARNING_IND ASH_TITLE ASH_ADDRESS ASH_TELEPHONE ASH_GENDER ASH_BIRTH_DATE ASH_AGE ASH_WORKER_ID ASH_TEAM_ID ASH_WORKER_NAME ASH_TEAM_NAME ASH_ASSESSMENT_TYPE ASH_START_DATE ASH_SCORE ASH_NI_NO ASH_NHS_NO ASH_GP_NAME ASH_GP_ADDRESS ASH_GP_TELEPHONE ASH_MC_NAME ASH_MC_ADDRESS ASH_MC_TELEPHONE ASH_CREATED_DATE ASH_CREATED_BY ASH_BIRTH_DATE_EST_IND ASH_PROVIS_PRIORITY_CODE ASH_PROVIS_PRIORITY
A747034 520980 P Mr First Test N Address Not Known, NO KNO M 01-JAN-50 60 E3061 EXA2 Natalie Roberts Exeter Hospital Team DECISION SUPPORT TOOL V3 26-NOV-10 26-NOV-10 NATROBER Y
A792323 520980 P Mr First Test N Address Not Known, NO KNO M 01-JAN-50 61 E2884 CDT1 Teresa Madden Care Direct BLUE BADGE ASSESSMENT 23-FEB-11 23-FEB-11 TMADDEN Y
A893303 520980 P Mr First Test N New Out Of County Address, NO OOC M 01-JAN-50 61 E3864 SSU Natalie Goddard System Support Unit Blue Badge Assessment 31-AUG-11 31-AUG-11 NGODDARD Y
A1038363 520980 P Mr First Test N New Out Of County Address, NO OOC M 01-JAN-50 62 E3221 EXA2 Regan Aldridge Exeter Hospital Team Support Plan FACE v6 17-MAY-12 0 17-MAY-12 RALDRIDG Y
A1052093 520980 P Mr First Test N New Out Of County Address, NO OOC M 01-JAN-50 62 E4021 CDT1 Caroline Bass Care Direct BLUE BADGE ASSESSMENT 12-JUN-12 12-JUN-12 CBASS Y
A1097901 520980 P Mr First Test N New Out Of County Address, NO OOC M 01-JAN-50 62 E4556 CDPS Owain Bale Southern Devon Cdp Team B.I.C.A. - FACE v6 24-AUG-12 0 24-AUG-12 OBALE Y
A1119829 520980 P Mr First Test N New Out Of County Address, NO OOC M 01-JAN-50 62 E3354 CDT1 Emma Willis Care Direct BLUE BADGE ASSESSMENT 02-OCT-12 02-OCT-12 EWILLIS Y
A1545747 520980 P Mr First Test N New Out Of County Address, NO OOC M 01-JAN-50 64 E5934 SEN1 Fiona Kilbey - Sensory Sensory Team SCR/Community Enabling Referral Form 11-JUN-14 11-JUN-14 FFKILBEY Y
A1601861 520980 P Mr First Test N New Out Of County Address, NO OOC M 01-JAN-45 69 E5112 SSD Robert Weeks Social Services Department My Assessment 28-AUG-14 28-AUG-14 RWEEKS Y


The PK and Index
o_persons PK per_id
o_assessments PK asm_id index asm_id (x1) asm_qsa_id (x2)
o_assessmnet_header FK ash_asm_id_nn index (x1)

I hope you can make sense of it all,

Many thanks,

Steven
 
Upvote 0 Downvote
try following - and this may still be incorrect as without knowing exactly how your relations work we can't say for sure but looking at column names and date it seems logical
Code: 
SELECT p.per_id,
p.per_first_names || ' ' || p.per_surname asFull_Name,
ass.asm_subject_id,
ah.ash_assessment_type
FROM o_persons p

INNER JOIN o_assessments ass
on p.per_id = ass.asm_subject_id
INNER JOIN o_assessment_header ah
on ah.ash_subject_id = p.per_id
[highlight #CC0000]and ah.ASH_ASM_ID = ass.ASM_ID[/highlight]
WHERE ass.asm_context like '%ADULT%' and p.per_id = '520980' 
ORDER BY p.per_id;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Frederico - that seems to have worked!!

Thank you!

To help me learn from my sistake - what did I do wrong?

Steven
 
Upvote 0 Downvote
from looking at your data and field names it looks like the relationships should be as follows

person can have several assessments - this is recorded on table o_assessment_header linked by field per_id to ash_subject_id
each assessment header has some of its details stored on o_assessment - link to header by ASH_ASM_ID to ASM_ID

The fact that both o_assessment and o_assessment_header have field subject_id (which links to o_person) leads me to believe that the original design either didn't have one of the tables or was poorly designed - subject_id should only be in one of them, in my opinion o_assessment_header (normal design would be for the header to be the master table), and o_assessment would only contain the link to the header


so what you were missing is the correct relation between o_assessment_header and o_assessment which may be what I gave you.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Detonah
  • Locked
  • Question
Micros Res 3700 - Cashier Report Displaying Multiple Days Together
Replies
0
Views
476
Detonah
Detonah
Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
484
Andrzejek
Andrzejek
TheLazyPig
  • Locked
  • Question
Merge Into with multiple execution 1
Replies
1
Views
474
carp
carp
Livia8
  • Locked
  • Question
Generate report/output on the basis of &quot;enquiry&quot; responsibilities only 1
Replies
16
Views
237
Livia8
Livia8
annie1975
  • Locked
  • Question
sql script in an cron job script failing
Replies
3
Views
213
carp
carp

Part and Inventory Search

Sponsor

Back
Top