SQL-Select ends up in an Error when combined

Question

I try to do a simple select for some duplicates but my sql statement always end up with this error:

Command not properly ended

What I have is a table like this

EAN              STR
=============    ====
8030524096397    AAAA
8030524096397    BBBB
8030524096731    XXXX
8030524096731    YYYY
8030524096324    CCCC

My select is actually simple

SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1;

Reults:

EAN               COUNT(*)
=============     ========
8030524096397        2
8030524096731        2

Everything is fine until here! Now I want the STR of the duplicates and try this

SELECT * FROM EAN E 
    INNER JOIN ( SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1 )  
R ON 
E.EAN = R.EAN;

But this results this error.

It exactly says this:

SELECT * FROM EAN E
INNER JOIN ( SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1 )  R ON
^
Error: Command not properly ended

What am I doing wrong?

Information to DB: Gupta Centura SQLBase 7.0.1

Answer 1

I don't think SQLBase 7.01 supports proper ANSI JOIN syntax (aside: what a good reason to use a more modern product). The error indicates a problem on the INNER JOIN.

Here are two possible solutions.

First, yucky archaic join syntax:

SELECT *
FROM EAN E,
     ( SELECT EAN, COUNT(*) as cnt FROM ean GROUP BY ean HAVING COUNT(*) > 1 )  
R 
WHERE E.EAN = R.EAN;

Second, IN:

SELECT *
FROM EAN E
WHERE E.EAN IN ( SELECT EAN FROM ean GROUP BY ean HAVING COUNT(*) > 1 )  

Answer 2

Try this (adding aliases to the tables with AS keyword)

SELECT * FROM EAN AS E 
INNER JOIN 
(SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1) AS R 
ON 
E.EAN = R.EAN;

Answer 3

SELECT * 
FROM EAN E 
INNER JOIN 
    (SELECT EA.EAN, COUNT(*) Cnt 
    FROM ean EA
    GROUP BY EA.ean 
    HAVING COUNT(*) > 1 
    ) R ON E.EAN = R.EAN;

I gave an alias Cnt to COUNT(*). This should make the error go away.