"data_2" error marcia hon  2018-03-05 11:08
Status: Closed
 
Hello,

How do I fix this error:

View has errors
org.postgresql.util.PSQLException: ERROR: table name "data_2" specified more than once


My script is the following:

    SELECT G1.SeriesName AS SeriesName, G1.Locus AS Locus, COUNT(DISTINCT G1.LabID) AS ConflictingGenotypes
    FROM NRG01_CMH_assay_Genotyping AS G1
        INNER JOIN NRG01_CMH_assay_Genotyping AS G2
                ON G1.ParticipantID = G2.ParticipantID
                   AND G1.Locus = G2.Locus
                   AND (G1.Allele1 <> G2.Allele1 OR G1.Allele2 <> G2.Allele2)
       GROUP BY G1.SeriesName, G1.Locus
 
 
Jon (LabKey DevOps) responded:  2018-03-09 15:49
Hi Marcia,

The error doesn't make sense against the query you pasted into the post since there is no table called "data_2" anywhere inside of it.

Is that NGR01_CMH_assay_Genotyping an actual table or is it a query made from a table?

Does the original query work without the INNER JOIN?

Have you tried to rewrite this as a Self JOIN?

https://www.w3schools.com/sql/sql_join_self.asp

Regards,

Jon