how to generate missing records in a join

skyline01 — Thu, 21 Mar 2019 13:33:32 GMT

I have table Tickets like:

Tickets:
Load * Inline [
TicketID, ChangeID
1, 10
2, 20
3,
4, 40
5,
6, 60
7, 60
8, 80
9, 90
]
;

Not all tickets have an associated change. Every ticket will have at most 1 associated change. A given change can be associated with 1+ tickets.

I have table Change_Impacted_Locations, like:

Change_Impacted_Locations:
Load * Inline [
ChangeID, Location
10, a
40, b
40, c
60, d
60, e
90, a
]
;

Not every change will have a location impacted record. A change can impact 1+ locations. Naturally, a location can be associated with 1+ locations.

I need to append a dummy record to my Change_Impacted_Locations table for all distinct ChangeIDs (where populated) on the Tickets table. So, I need to generate something like:

Missing_Locations:
Load * Inline [
ChangeID, Location
20, dummy
80, dummy
]
;

And, I need these records concatenated into my Change_Impacted_Locations table.

How can I achieve this?

Re: how to generate missing records in a join

skyline01 — Tue, 26 Mar 2019 22:08:24 GMT

I figured out the solution to my problem.

//need ChangeID name to be distinct
Tickets:
Load * Inline [
TicketID, ChangeID on Tickets
1, 10
2, 20
3,
4, 40
5,
6, 60
7, 60
8, 80
9, 90
]
;

//need ChangeID name to be distinct
Change_Impacted_Locations:
Load * Inline [
ChangeID on Impacted Locations, Location
10, a
40, b
40, c
60, d
60, e
90, a
]
;

//will autoconcatenate to the Change_Impacted_Locations table
Load
	"ChangeID on Tickets" as "ChangeID on Impacted Locations"
	,'dummy' as Location
Resident
	Tickets
Where
	Not Exists("ChangeID on Impacted Locations", "ChangeID on Tickets")
;

topic Re: how to generate missing records in a join in App Development

how to generate missing records in a join

Re: how to generate missing records in a join