Greetings . . .

I am using Elevate DB 2.05b6.

I have a table
CREATE TABLE "Tests"
("TestGroup" VARCHAR(5) COLLATE "ANSI_CI" NOT NULL,
"Test" VARCHAR(5) COLLATE "ANSI_CI" NOT NULL,
CONSTRAINT "indPrimaryKey" PRIMARY KEY ("TestGroup", "Test"))

The table stores pathology tests that are grouped into TestGroups.  
Sample data is:
TestGroup, Test
-------------------
FAT, CHOL
FAT, HDL
FAT, LDL
FAT, TRIG
FATS, CHOL
FATS, HDL
FATS, LDL
FATS, TRIG
LIPID, HDL
LIPID, LDL

I want to find which groups are duplicated.  In the above list,  FATS and FAT contain the same tests.
The following query gives a list of the TestGroups that have a common Test.  This works properly.

SELECT T1.TestGroup, T2.TestGroup, T1.Test
 FROM Tests AS T1
   INNER JOIN
     Tests AS T2
        ON T1.Test = T2.Test AND T1.TestGroup < T2.TestGroup
GROUP BY T1.TestGroup, T2.TestGroup, T1.Test

TestGroup1, TestGroup2, Test
FAT, FATS, CHOL
FAT, FATS, HDL
FAT, FATS, LDL
FAT, FATS, TRIG
FAT, LIPID, HDL
FAT, LIPID, LDL
FATS, LIPID, HDL
FATS, LIPID, LDL

Next step:
------------
Grouping the results on T1.TestGroup, T2.TestGroup and counting the number of rows, I get different results for 2 different versions of the HAVING clause.

SELECT T1.TestGroup, T2.TestGroup,
    Count(*) AS C1, Count(T2.Test) AS C2,
    (SELECT COUNT(*) FROM Tests AS T3 WHERE T3.TestGroup = T1.TestGroup) AS C3,
    (SELECT COUNT(*) FROM Tests AS T4 WHERE T4.TestGroup = T2.TestGroup) AS C4
 FROM Tests AS T1
    INNER JOIN
        Tests AS T2
            ON T1.Test = T2.Test AND T1.TestGroup < T2.TestGroup
GROUP BY T1.TestGroup, T2.TestGroup
HAVING
--count(*) = (SELECT COUNT(*) FROM Tests AS T5 WHERE T5.TestGroup = T1.TestGroup)  AND
--count(*) = (SELECT COUNT(*) FROM Tests AS T6 WHERE T6.TestGroup = T2.TestGroup)
  C1 = C3 AND C2 = C4

Using HAVING  C1 = C3 AND C2 = C4 gives the correct results.  That is, FATS, FAT

Using HAVING
COUNT(*) = (SELECT COUNT(*) FROM Tests AS T5 WHERE T5.TestGroup = T1.TestGroup)  AND
COUNT(*) = (SELECT COUNT(*) FROM Tests AS T6 WHERE T6.TestGroup = T2.TestGroup)
does not return any rows.

I think they should be the same or is there something that I am missing?

Richard,

As far as I can see the syntax looks equivalent.  It looks like the correlated subquery in the having clause is not
being evaluated correctly.

John

Thank you John.   

<<As far as I can see the syntax looks equivalent.  It looks like the correlated subquery in the having clause is not
being evaluated correctly.>>

Richard Harding

Richard,

<< I think they should be the same or is there something that I am missing?
>>

Did you try this with the latest version to see if the results are different
?  If they aren't, please send me the database catalog and relevant tables
via email, and I'll take a look.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

<<Did you try this with the latest version to see if the results are different?  If they aren't, please send me the database catalog and relevant tables via email, and I'll take a look.>>

Thanks Tim

I'll wait until the next release and give it a go.

Richard Harding