Not loading data for deleted items

Markbhai — Thu, 25 Apr 2024 18:54:46 GMT

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

Re: Not loading data for deleted items

BrunPierre — Thu, 25 Apr 2024 20:16:43 GMT

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;

topic Re: Not loading data for deleted items in App Development

Not loading data for deleted items

Re: Not loading data for deleted items