Subject Re: Self-Joined Items Master and transactions stock ledger
Author Bhavbhuti Nathwani
Hi Svein

The TRIM() is now gone from the code. I had some old data-structure and newer values in it so JobID is down to 10 chars :)

If gttItemsSelected.ID column contains unique values only for a given gttItemsSelected.JobID. Between jobs the gttItemsSelected.ID might repeat. gttItemsSelected.UniqueID is absolutely unique but has no referential value (surrogate key(?)). No index is created for any of the gttItemsSelected table columns. Once a job is done the records are deleted based on the JobID. Yes, mItems will contain many records and only few IDs (as low as one) will end up in gttItemsSelected table when reporting.

FYI gttItemsSelected table is a regular table that I use to populate with item ids in the ID column as per the selection of the user, I can't use pure GTT that FireBird allows due to various issues in my another recent thread on this forum.

But I see the efficiency you are suggesting in using a JOIN. Will it affect much if I change your suggested
> JOIN gttItemsSelected gis on p.iID = gis.ID
> WHERE TRIM(gis.JobID) = '_2M60RJ9T4'
JOIN gttItemsSelected gis on p.iID = gis.ID
AND TRIM(gis.JobID) = '_2M60RJ9T4'

Thanks for your continued support, please note I am not a master SQL writer so sorry about writing queries they way I have.

Kind regards.

--- In, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
> Perfect? Well, then, if gttItemsSelected.ID is unique, I would recommend changing to