Not loading data for deleted items

[Markbhai]

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

[BrunPierre · Accepted solution]

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;