materialized_genoview

SELECT stock.stock_id AS accession_id,

    nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,

    nd_experiment_project.project_id AS genotyping_project_id,

    genotype.genotype_id,

    stock_type.name AS stock_type

   FROM (((((((stock

     JOIN cvterm stock_type ON (((stock_type.cvterm_id = stock.type_id) AND ((stock_type.name)::text = 'accession'::text))))

     JOIN nd_experiment_stock ON ((stock.stock_id = nd_experiment_stock.stock_id)))

     JOIN nd_experiment_protocol ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id)))

     LEFT JOIN nd_experiment_project ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id)))

     JOIN nd_protocol ON ((nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id)))

     JOIN nd_experiment_genotype ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id)))

     JOIN genotype ON ((genotype.genotype_id = nd_experiment_genotype.genotype_id)))

  GROUP BY stock.stock_id, nd_experiment_protocol.nd_protocol_id, nd_experiment_project.project_id, genotype.genotype_id, stock_type.name

UNION

 SELECT accession.stock_id AS accession_id,

    nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,

    nd_experiment_project.project_id AS genotyping_project_id,

    nd_experiment_genotype.genotype_id,

    stock_type.name AS stock_type

   FROM ((((((((stock accession

     JOIN stock_relationship ON (((accession.stock_id = stock_relationship.object_id) AND (stock_relationship.type_id IN ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = ANY (ARRAY[('tissue_sample_of'::character varying)::text, ('plant_of'::character varying)::text, ('plot_of'::character varying)::text])))))))

     JOIN stock ON (((stock_relationship.subject_id = stock.stock_id) AND (stock.type_id IN ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = ANY (ARRAY[('tissue_sample'::character varying)::text, ('plant'::character varying)::text, ('plot'::character varying)::text])))))))

     JOIN cvterm stock_type ON ((stock_type.cvterm_id = stock.type_id)))

     JOIN nd_experiment_stock ON ((stock.stock_id = nd_experiment_stock.stock_id)))

     JOIN nd_experiment_protocol ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id)))

     LEFT JOIN nd_experiment_project ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id)))

     JOIN nd_protocol ON ((nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id)))

     JOIN nd_experiment_genotype ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id)))

  GROUP BY accession.stock_id, nd_experiment_protocol.nd_protocol_id, nd_experiment_project.project_id, nd_experiment_genotype.genotype_id, stock_type.name

  ORDER BY 1, 2, 3, 4;