So I have multiple tables to store the data. My main table has 4 Identity columns for 4 other associated tables, based on the number chosen. I have gotten this to display and work.
The problem is that I also have 4 Index Organized Tables that hold the data for for my 1 to many relations. When I am attempting to link these tables it results in a duplicate record for each of the items selected, as seen below.
SELECT capa_plans.plan_id,
capa\_plans.capa\_number,
capa\_plans.issued\_by,
capa\_plans.issued\_to,
capa\_plans.date\_issued,
capa\_plans.date\_due,
capa\_types.capa\_type,
capa\_origins.capa\_origin,
capa\_risk\_levels.risk\_level\_name,
capa\_plans.audited\_area,
capa\_plans.quality\_events,
capa\_documents.document\_title,
capa\_standards.standard\_code,
capa\_parts.part\_number,
capa\_models.model\_name,
capa\_plans.general\_condition,
capa\_plans.details\_of\_nonconformance,
capa\_plans.evidence\_found,
capa\_plans.root\_cause,
capa\_plans.short\_term,
capa\_plans.short\_term\_date,
capa\_plans.long\_term,
capa\_plans.long\_term\_date,
capa\_plans.responded\_by,
capa\_plans.responded\_date,
capa\_plans.verification\_evidence,
capa\_plans.verification\_action\_required,
capa\_plans.verified\_by,
capa\_plans.verified\_date,
capa\_plans.action\_required,
capa\_plans.validated\_by,
capa\_plans.validated\_date,
capa\_plans.delinquent,
capa\_statuses.capa\_status
FROM capa_plans,
capa\_types,
capa\_origins,
capa\_statuses,
capa\_risk\_levels,
capa\_plan\_parts,
capa\_parts,
capa\_plan\_documents,
capa\_documents,
capa\_plan\_models,
capa\_models,
capa\_plan\_standards,
capa\_standards
WHERE capa_plans.capa_type_id = capa_types.capa_type_id
AND capa\_plans.capa\_origin\_id = capa\_origins.capa\_origin\_id
AND capa\_plans.capa\_status\_id = capa\_statuses.capa\_status\_id
AND capa\_plans.risk\_level\_id = capa\_risk\_levels.risk\_level\_id
AND capa\_plans.plan\_id = capa\_plan\_parts.plan\_id
AND capa\_plan\_parts.part\_id = capa\_parts.part\_id
AND capa\_plans.plan\_id = capa\_plan\_documents.plan\_id
AND capa\_plan\_documents.document\_id = capa\_documents.document\_id
AND capa\_plans.plan\_id = capa\_plan\_models.plan\_id
AND capa\_plan\_models.model\_id = capa\_models.model\_id
AND capa\_plans.plan\_id = capa\_plan\_standards.plan\_id
AND capa\_plan\_standards.standard\_id = capa\_standards.standard\_id
I was attempting to have a single record that displayed the multiple items in a single column for each record. So a resulting 4 records only with the columns DOCUMENT_TITLE, STANDARD_CODE, PART_NUMBER, and MODEL_NAME being allowed to display the multiple items in one row.
I have received some help with this but not to what I was hoping to complete. This can be seen below as well.
SELECT CASE WHEN rn = 1 THEN plan_id ELSE NULL END plan_id,
CASE WHEN rn = 1 THEN capa\_number ELSE NULL END capa\_number,
CASE WHEN rn = 1 THEN issued\_by ELSE NULL END issued\_by,
CASE WHEN rn = 1 THEN issued\_to ELSE NULL END issued\_to,
CASE WHEN rn = 1 THEN date\_issued ELSE NULL END date\_issued,
CASE WHEN rn = 1 THEN date\_due ELSE NULL END date\_due,
CASE WHEN rn = 1 THEN audited\_area ELSE NULL END audited\_area,
CASE WHEN rn = 1 THEN quality\_events ELSE NULL END quality\_events,
CASE WHEN rn = 1 THEN general\_condition ELSE NULL END
general\_condition,
CASE WHEN rn = 1 THEN details\_of\_nonconformance ELSE NULL END
details\_of\_nonconformance,
CASE WHEN rn = 1 THEN evidence\_found ELSE NULL END evidence\_found,
CASE WHEN rn = 1 THEN root\_cause ELSE NULL END root\_cause,
CASE WHEN rn = 1 THEN short\_term ELSE NULL END short\_term,
CASE WHEN rn = 1 THEN short\_term\_date ELSE NULL END short\_term\_date,
CASE WHEN rn = 1 THEN long\_term ELSE NULL END long\_term,
CASE WHEN rn = 1 THEN long\_term\_date ELSE NULL END long\_term\_date,
CASE WHEN rn = 1 THEN responded\_by ELSE NULL END responded\_by,
CASE WHEN rn = 1 THEN responded\_date ELSE NULL END responded\_date,
CASE WHEN rn = 1 THEN verification\_evidence ELSE NULL END
verification\_evidence,
CASE WHEN rn = 1 THEN verification\_action\_required ELSE NULL END
verification\_action\_required,
CASE WHEN rn = 1 THEN verified\_by ELSE NULL END verified\_by,
CASE WHEN rn = 1 THEN verified\_date ELSE NULL END verified\_date,
CASE WHEN rn = 1 THEN action\_required ELSE NULL END action\_required,
CASE WHEN rn = 1 THEN validated\_by ELSE NULL END validated\_by,
CASE WHEN rn = 1 THEN validated\_date ELSE NULL END validated\_date,
CASE WHEN rn = 1 THEN delinquent ELSE NULL END delinquent,
part\_number,
document\_title,
model\_name,
standard\_code,
capa\_status,
risk\_level\_name,
capa\_origin,
capa\_type
FROM ( SELECT capa_plans.plan_id,
capa\_plans.capa\_number,
capa\_types.capa\_type,
capa\_origins.capa\_origin,
capa\_statuses.capa\_status,
capa\_risk\_levels.risk\_level\_name,
capa\_plans.issued\_by,
capa\_plans.issued\_to,
capa\_plans.date\_issued,
capa\_plans.date\_due,
capa\_plans.audited\_area,
capa\_plans.quality\_events,
capa\_plans.general\_condition,
capa\_plans.details\_of\_nonconformance,
capa\_plans.evidence\_found,
capa\_plans.root\_cause,
capa\_plans.short\_term,
capa\_plans.short\_term\_date,
capa\_plans.long\_term,
capa\_plans.long\_term\_date,
capa\_plans.responded\_by,
capa\_plans.responded\_date,
capa\_plans.verification\_evidence,
capa\_plans.verification\_action\_required,
capa\_plans.verified\_by,
capa\_plans.verified\_date,
capa\_plans.action\_required,
capa\_plans.validated\_by,
capa\_plans.validated\_date,
capa\_plans.delinquent,
capa\_parts.part\_number,
capa\_documents.document\_title,
capa\_models.model\_name,
capa\_standards.standard\_code,
ROW\_NUMBER ()
OVER (PARTITION BY capa\_plans.plan\_id
ORDER BY capa\_plans.plan\_id)
rn
FROM capa\_plans,
capa\_types,
capa\_origins,
capa\_statuses,
capa\_risk\_levels,
capa\_plan\_parts,
capa\_parts,
capa\_plan\_documents,
capa\_documents,
capa\_plan\_models,
capa\_models,
capa\_plan\_standards,
capa\_standards
WHERE capa\_plans.capa\_type\_id = capa\_types.capa\_type\_id
AND capa\_plans.capa\_origin\_id = capa\_origins.capa\_origin\_id
AND capa\_plans.capa\_status\_id = capa\_statuses.capa\_status\_id
AND capa\_plans.risk\_level\_id = capa\_risk\_levels.risk\_level\_id
AND capa\_plans.plan\_id = capa\_plan\_parts.plan\_id
AND capa\_plan\_parts.part\_id = capa\_parts.part\_id
AND capa\_plans.plan\_id = capa\_plan\_documents.plan\_id
AND capa\_plan\_documents.document\_id =
capa\_documents.document\_id
AND capa\_plans.plan\_id = capa\_plan\_models.plan\_id
AND capa\_plan\_models.model\_id = capa\_models.model\_id
AND capa\_plans.plan\_id = capa\_plan\_standards.plan\_id
AND capa\_plan\_standards.standard\_id =
capa\_standards.standard\_id
ORDER BY plan\_id)
Does any one know if I have gone astray in attempting to normalize my data and now am not able to complete what I intended? Or, am I just missing how to join these tables correctly, if so how?
Also if you would like to take a look at the app itself
Workspace: CAPA_Help
Username: helpme
Password: abc123
Application: 47705 Corrective Actions
Page: 2 CAPA Reports