select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  1 = 1 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      1 = 1 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            tbl_sites.site_id :: text in ('128', '151', '91')
          ) 
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  1 = 1 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;
select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  1 = 1 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      1 = 1 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            tbl_sites.site_id :: text in ('128', '151', '91')
          ) 
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  1 = 1 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;
select 
  site_name 
from 
  tbl_sample_groups 
  join tbl_sites on tbl_sample_groups.site_id = tbl_sites.site_id 
where 
  tbl_sample_groups.sample_group_id = 141 
  and tbl_sites.site_id in (
    select 
      distinct tbl_sites.site_id 
    from 
      tbl_sites 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  site_name 
order by 
  site_name;
select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  tbl_sample_groups.sample_group_id = 141 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  tbl_methods.method_name, 
  aepm.method_name as prep_method_name, 
  tbl_methods.method_abbrev_or_alt_name, 
  tbl_methods.description, 
  tbl_record_types.record_type_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods on tbl_datasets.method_id = tbl_methods.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on tbl_methods.record_type_id = tbl_record_types.record_type_id 
where 
  tbl_physical_samples.sample_group_id = 141 
group by 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  tbl_methods.method_name, 
  tbl_methods.method_abbrev_or_alt_name, 
  tbl_methods.description, 
  prep_method_name, 
  tbl_record_types.record_type_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  tbl_physical_samples.sample_group_id = 141 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      tbl_physical_samples.sample_group_id = 141 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            tbl_sites.site_id :: text in ('128', '151', '91')
          ) 
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  tbl_physical_samples.sample_group_id = 141 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  tbl_physical_samples.sample_group_id = 141 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '151', '91')
      )
  ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;
select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('103')
      )
  ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  1 = 1 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('103')
      )
  ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      1 = 1 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            tbl_sites.site_id :: text in ('103')
          ) 
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('103')
      )
  ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  1 = 1 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        tbl_sites.site_id :: text in ('103')
      )
  ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;
select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  1 = 1 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      1 = 1 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
          ) 
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  1 = 1 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;
select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  1 = 1 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      1 = 1 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
          ) 
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  1 = 1 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        tbl_sites.site_id :: text in ('128', '91', '103', '151', '190')
      )
  ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;
select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  1 = 1 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      1 = 1 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            (
              tbl_sites.site_id :: text in ('131', '7')
            )
          ) -- Make nice ANDING Thanks Roger Mähler
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  1 = 1 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  1 = 1 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;
select 
  site_name 
from 
  tbl_sample_groups 
  join tbl_sites on tbl_sample_groups.site_id = tbl_sites.site_id 
where 
  tbl_sample_groups.sample_group_id = 336 
  and tbl_sites.site_id in (
    select 
      distinct tbl_sites.site_id 
    from 
      tbl_sites 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) 
group by 
  site_name 
order by 
  site_name;
select 
  tbl_sample_groups.site_id as site_id, 
  sample_group_name as "Sample group name", 
  sample_group_description, 
  method_name 
from 
  tbl_sample_groups 
  left join tbl_methods on tbl_sample_groups.method_id = tbl_methods.method_id 
where 
  tbl_sample_groups.sample_group_id = 336 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) 
group by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name 
order by 
  tbl_sample_groups.site_id, 
  sample_group_name, 
  sample_group_description, 
  method_name;
select 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  tbl_methods.method_name, 
  aepm.method_name as prep_method_name, 
  tbl_methods.method_abbrev_or_alt_name, 
  tbl_methods.description, 
  tbl_record_types.record_type_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods on tbl_datasets.method_id = tbl_methods.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on tbl_methods.record_type_id = tbl_record_types.record_type_id 
where 
  tbl_physical_samples.sample_group_id = 336 
group by 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  tbl_methods.method_name, 
  tbl_methods.method_abbrev_or_alt_name, 
  tbl_methods.description, 
  prep_method_name, 
  tbl_record_types.record_type_name;
select 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
from 
  tbl_physical_samples 
  join tbl_analysis_entities on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
where 
  tbl_physical_samples.sample_group_id = 336 
  and tbl_physical_samples.physical_sample_id in (
    select 
      distinct tbl_physical_samples.physical_sample_id 
    from 
      tbl_physical_samples 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) 
group by 
  sample_name, 
  tbl_physical_samples.physical_sample_id 
order by 
  sample_name;
select 
  species, 
  array_to_string(
    array_agg(
      physical_sample_id :: text || '|' || abundance
    ), 
    ';'
  ) as species_list_by_physical_sample_id 
from 
  (
    select 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species as species, 
      tbl_physical_samples.physical_sample_id, 
      sum(tbl_abundances.abundance) as abundance 
    from 
      tbl_datasets 
      left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
      left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
      left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_methods on tbl_analysis_entity_prep_methods.method_id = tbl_methods.method_id 
      left join tbl_abundances on tbl_abundances.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
      left join tbl_taxa_tree_master on tbl_abundances.taxon_id = tbl_taxa_tree_master.taxon_id 
      left join tbl_taxa_tree_genera on tbl_taxa_tree_master.genus_id = tbl_taxa_tree_genera.genus_id 
    where 
      tbl_physical_samples.sample_group_id = 336 
      and tbl_taxa_tree_master.taxon_id in (
        select 
          distinct tbl_taxa_tree_master.taxon_id 
        from 
          tbl_taxa_tree_master 
          left join tbl_abundances on tbl_abundances."taxon_id" = tbl_taxa_tree_master."taxon_id" 
          left join tbl_analysis_entities on tbl_abundances."analysis_entity_id" = tbl_analysis_entities."analysis_entity_id" 
          left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
          left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
          inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
          left join tbl_taxa_tree_genera on tbl_taxa_tree_master."genus_id" = tbl_taxa_tree_genera."genus_id" 
          left join tbl_taxa_tree_authors on tbl_taxa_tree_master."author_id" = tbl_taxa_tree_authors."author_id" 
        where 
          (
            (
              tbl_sites.site_id :: text in ('131', '7')
            )
          ) -- Make nice ANDING Thanks Roger Mähler
          and (
            tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species || ' (' || tbl_taxa_tree_authors.author_name || ')'
          ) is not null
      ) 
    group by 
      tbl_taxa_tree_genera.genus_name || ' ' || tbl_taxa_tree_master.species, 
      tbl_physical_samples.physical_sample_id 
    order by 
      species, 
      physical_sample_id
  ) as t 
group by 
  species;
select 
  tbl_datasets.dataset_id, 
  dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text as column_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name, 
  aepm.method_name as prep_method_name 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_analysis_entities.dataset_id = tbl_datasets.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_sample_groups on tbl_physical_samples.sample_group_id = tbl_sample_groups.sample_group_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  left join tbl_record_types on dm.record_type_id = tbl_record_types.record_type_id 
where 
  tbl_physical_samples.sample_group_id = 336 
  and tbl_sample_groups.sample_group_id in (
    select 
      distinct tbl_sample_groups.sample_group_id 
    from 
      tbl_sample_groups 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) 
group by 
  prep_method_name, 
  column_id, 
  tbl_datasets.dataset_id, 
  tbl_datasets.dataset_name, 
  dm.method_name, 
  dm.method_abbrev_or_alt_name, 
  dm.description, 
  tbl_record_types.record_type_name;
select 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name, 
  array_to_string(
    array_agg(
      dm.method_id :: text || '_' || COALESCE(aepm.method_id :: text, 'NULL'):: text || '|' || to_char(
        tbl_measured_values.measured_value, 
        '999.999'
      )
    ), 
    ';'
  ) as dataset_value_composites 
from 
  tbl_datasets 
  left join tbl_analysis_entities on tbl_datasets.dataset_id = tbl_analysis_entities.dataset_id 
  left join tbl_analysis_entity_prep_methods on tbl_analysis_entity_prep_methods.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
  left join tbl_physical_samples on tbl_analysis_entities.physical_sample_id = tbl_physical_samples.physical_sample_id 
  left join tbl_methods dm on tbl_datasets.method_id = dm.method_id 
  left join tbl_methods aepm on tbl_analysis_entity_prep_methods.method_id = aepm.method_id 
  inner join tbl_measured_values on tbl_measured_values.analysis_entity_id = tbl_analysis_entities.analysis_entity_id 
where 
  tbl_physical_samples.sample_group_id = 336 
  and tbl_analysis_entities.analysis_entity_id in (
    select 
      distinct tbl_analysis_entities.analysis_entity_id 
    from 
      tbl_analysis_entities 
      left join tbl_physical_samples on tbl_analysis_entities."physical_sample_id" = tbl_physical_samples."physical_sample_id" 
      left join tbl_sample_groups on tbl_physical_samples."sample_group_id" = tbl_sample_groups."sample_group_id" 
      inner join tbl_sites on tbl_sample_groups."site_id" = tbl_sites."site_id" 
      left join tbl_datasets on tbl_analysis_entities."dataset_id" = tbl_datasets."dataset_id" 
    where 
      (
        (
          tbl_sites.site_id :: text in ('131', '7')
        )
      ) -- Make nice ANDING Thanks Roger Mähler
      ) -- extra condition needed for filter by user
group by 
  tbl_physical_samples.physical_sample_id, 
  tbl_physical_samples.sample_name;