Morning all.

I'm trying to create a simple 'de-normalised' taxon dictionary containing just recommended taxon list items that we will then take out of Recorder into our Postgres database to do spatial work with. This taxon dictionary will be joined to a simple de-normalised list of records, and a taxon groups table, therefore keeping the Postgres structure as simple as possible.

I am using the following script that seems to work fine - but I'm just a little nervous that I may have missed something and would be grateful if someone with a better understanding of R6 could cast their eye over it.

Here is the script (including the create table code in case someone wants to have a shot at running it).

Many thanks,
Steve

-- *****************************************************************************************************
-- Taxon dictionary table.
CREATE TABLE [dbo].[denormal_taxon_dict] (
  [rtvk] [varchar](16)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [rtlik] [varchar](16)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [actual_name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [common_name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [preferred_name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [taxon_family] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [taxon_order] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [taxon_group_key] [varchar](16)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [taxon_group_name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--GO
;
-- *****************************************************************************************************
-- Clear all the tables.
TRUNCATE TABLE [dbo].[denormal_taxon_dict];
;
-- *****************************************************************************************************
-- Insert into denormal_taxon_dict
INSERT INTO  [dbo].[denormal_taxon_dict]
(
  rtvk,
  rtlik,
  actual_name,
  common_name,
  preferred_name,
  taxon_family,
  taxon_order,
  taxon_group_key,
  taxon_group_name
)
SELECT DISTINCT
NS.RECOMMENDED_TAXON_VERSION_KEY,
NS.RECOMMENDED_TAXON_LIST_ITEM_KEY,
ITN.ACTUAL_NAME,
ITN.COMMON_NAME,
ITN.PREFERRED_NAME,
ITN_FAMILY.PREFERRED_NAME AS FAMILY,
ITN_ORDER.PREFERRED_NAME AS "ORDER",
--TV.TAXON_VERSION_KEY,
--TV.OUTPUT_GROUP_KEY,
TG.TAXON_GROUP_KEY,
TG.TAXON_GROUP_NAME

FROM dbo.NAMESERVER NS
--FAMILY
INNER JOIN dbo.INDEX_TAXON_GROUP ITG_FAMILY ON NS.RECOMMENDED_TAXON_LIST_ITEM_KEY = ITG_FAMILY.CONTAINED_LIST_ITEM_KEY
INNER JOIN dbo.TAXON_LIST_ITEM TLI2 ON TLI2.TAXON_LIST_ITEM_KEY = ITG_FAMILY.Taxon_List_Item_Key AND TLI2.TAXON_RANK_KEY = 'NBNSYS0000000018'--12-order;18-family
LEFT JOIN INDEX_TAXON_NAME ITN_FAMILY ON ITN_FAMILY.TAXON_LIST_ITEM_KEY = ITG_FAMILY.TAXON_LIST_ITEM_KEY

--ORDER
INNER JOIN dbo.INDEX_TAXON_GROUP ITG_ORDER ON NS.RECOMMENDED_TAXON_LIST_ITEM_KEY = ITG_ORDER.CONTAINED_LIST_ITEM_KEY
INNER JOIN dbo.TAXON_LIST_ITEM TLI3 ON TLI3.TAXON_LIST_ITEM_KEY = ITG_ORDER.Taxon_List_Item_Key AND TLI3.TAXON_RANK_KEY = 'NBNSYS0000000012'--12-order;18-family
LEFT JOIN INDEX_TAXON_NAME ITN_ORDER ON ITN_ORDER.TAXON_LIST_ITEM_KEY = ITG_ORDER.TAXON_LIST_ITEM_KEY

--EVERYTHING ELSE
LEFT JOIN dbo.TAXON_LIST_ITEM TLI ON NS.RECOMMENDED_TAXON_LIST_ITEM_KEY = TLI.TAXON_LIST_ITEM_KEY
LEFT JOIN dbo.INDEX_TAXON_NAME ITN ON TLI.TAXON_LIST_ITEM_KEY = ITN.TAXON_LIST_ITEM_KEY
LEFT JOIN dbo.TAXON_VERSION TV ON NS.RECOMMENDED_TAXON_VERSION_KEY = TV.TAXON_VERSION_KEY
LEFT JOIN dbo.TAXON_GROUP TG ON TV.OUTPUT_GROUP_KEY = TG.TAXON_GROUP_KEY
ORDER BY ITN.ACTUAL_NAME;

-- *****************************************************************************************************