Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all.
I have three tables which are linked through a common id (Management.id).
These are:
- Management table
- Details table; and
- Evidence table
When an item is deleted from the Management table in our front end we set a parameter [is deleted] to 1 and this is stored in the Management table (but none of the others).
When I join the tables through the Management.id I am loading up all of the data, but I only want to load up the records which have not been deleted.
- below is a cut down extract of the load script.
[tech_organizational_controls_management]:
LOAD
[id] AS [Management.Management_id],
[SOA_Version],
[is_Deleted],
[created_at],
[updated_at],
[standard_id];
SELECT id,
`SOA_Version`,
`is_Deleted`,
`created_at`,
`updated_at`,
`standard_id`
FROM `xxxxxxx`.`tech_organizational_controls_management`;
[tech_organizational_control_details]:
LOAD
[id] AS [control_details.id],
[control_owner] as [Control Owner],
[reason_control] as [Rationale],
[control_implement] as [Implemented],
[version_id] as [Details.Management.id],
[object_id],
SELECT id,
`control_owner`,
`reason_control`,
`control_implement`,
`version_id`,
`object_id`,
FROM `xxxxxxxxx`.`tech_organizational_control_details`;
[tech_organizational_controls_evidence]:
LOAD
[id] AS [evidence.id],
[evidence_type_value] as [Evidence Type],
[evidence_type] AS [Evidence Category],
[version_id] AS [Evidence.Management.id],
SELECT id,
`evidence_type_value`,
`evidence_type`,
`version_id`,
FROM `xxxxxxxx`.`tech_organizational_controls_evidence`;
What is my best approach?
Thanks
Mark
Often than not, Exists() proves to be an efficient way to filter data based on the existence of records in another table.
Filter:
LOAD [id] as LookupID
Where [is_Deleted] = 1;
SELECT id
FROM `xxxxxxx`.`tech_organizational_controls_management`;
[tech_organizational_controls_management]:
LOAD [id] AS [Management.Management_id],
...
Where not Exists(LookupID,[id]);
SELECT
...
FROM `xxxxxxx`.`tech_organizational_controls_management`;
[tech_organizational_control_details]:
LOAD [id] AS [control_details.id],
...
Where not Exists(LookupID,[id]);
SELECT
...
FROM `xxxxxxxxx`.`tech_organizational_control_details`;
[tech_organizational_controls_evidence]:
LOAD [id] AS [evidence.id],
...
Where not Exists(LookupID,[id]);
SELECT
...
FROM `xxxxxxxx`.`tech_organizational_controls_evidence`;
DROP Table Filter;
Often than not, Exists() proves to be an efficient way to filter data based on the existence of records in another table.
Filter:
LOAD [id] as LookupID
Where [is_Deleted] = 1;
SELECT id
FROM `xxxxxxx`.`tech_organizational_controls_management`;
[tech_organizational_controls_management]:
LOAD [id] AS [Management.Management_id],
...
Where not Exists(LookupID,[id]);
SELECT
...
FROM `xxxxxxx`.`tech_organizational_controls_management`;
[tech_organizational_control_details]:
LOAD [id] AS [control_details.id],
...
Where not Exists(LookupID,[id]);
SELECT
...
FROM `xxxxxxxxx`.`tech_organizational_control_details`;
[tech_organizational_controls_evidence]:
LOAD [id] AS [evidence.id],
...
Where not Exists(LookupID,[id]);
SELECT
...
FROM `xxxxxxxx`.`tech_organizational_controls_evidence`;
DROP Table Filter;