SQL select returns extra result

Question

Guess there are similar questions and the answere might is easy but I cant help my self and thats why I ask you guys.

I have some Data in a DB (Centura/Gupta SQLBase 7) no Left/Right Join possible - obviously not implemented in sqlbase sql

Here is my select

SELECT 
    I.IARTNR, 
    L.ARTNAME 
FROM 
    INVENTUR I,
    LAGER L 
WHERE 
    L.ARTSTR = I.IARTNR 
AND
    I.AB = '2015-81';

returns 20 rows, not 18 as expacted.

INVENTUR rows with AB set to 2015-81 are 18 and in LAGER there are <3000 rows. What I'm trying to do is select all articles von INVENTUR and add the article name thats written in LAGER.

Whats wrong with my select ? Running this "mysterious" since 3 days.

Answer 1

ANSI join syntax for Outer / Inner joins was added in v8.5 onwards ( now upto v12.1 ). Before v8.5 , you can use the native Gupta Outer / Inner join syntax e.g.

SELECT t1.col1,t2.col1,t1.col2,t2.col2
FROM t1,t2
WHERE t1.col1 = t2.col1(+)
AND   t1.col2 = t2.col2(+)

The next example lists customer names and their order numbers,including customers who have made no orders:

SELECT CUSTOMER.CUSTNO,NAME
FROM CUSTOMER,ORDERS
WHERE CUSTOMER.CUSTNO = ORDERS.CUSTNO(*)

The same query using ANSI syntax in SQLBase v8.5 onmwards is:

SELECT CUSTOMER.CUSTNO,NAME 
FROM CUSTOMER LEFT OUTER JOIN ORDERS ON CUSTOMER.CUSTNO = ORDERS.CUSTNO

Answer 2

Use explicit joins.

SELECT I.IARTNR, L.ARTNAME 
FROM INVENTUR I
INNER JOIN LAGER L ON I.IARTNR = L.ARTSTR
WHERE I.AB = '2015-81';

And if needs be DISTINCT.

SELECT DISTINCT I.IARTNR, L.ARTNAME 
FROM INVENTUR I
INNER JOIN LAGER L ON I.IARTNR = L.ARTSTR
WHERE I.AB = '2015-81';

Answer 3

Of course SQLBase has inner / outer joins ! Either Native syntax ( using (+) ) or ANSI .
Here's the syntax:

NATIVE: SELECT t1.col1, t2.col1, t1.col2, t2.col2 FROM t1, t2 WHERE t1.col1 = t2.col1 (+) AND t1.col2 = t2.col2 (+);

ANSI: SELECT t1.col1, t2.col1, t1.col2, t2.col2 FROM t2 RIGHT OUTER JOIN t1 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2 ;

p.s. SQLBase is no 'weird' database . v12 recently released will outstrip SQLServer every time in terms of performance , footprint and overall cost of ownership. Please be more aware of your facts before broadcasting nonsense.