the "minus" keyword

[halx]

Hello !

I have two tables, table1 and table2 and want to substract the 2 sets of data.

(select table1.field1, table1.field2
from table1)
minus
(select table2.field1, table2.field2
from table2)

does not work. I get a message error: "syntax error in union query"

Could anybody help me ?

Thanks

Alex

 

[blom0344]

Hello Alex,

DB2 SQL works with the set operator EXCEPT . Maybe this works in Jet SQL too. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[halx]

"Except" does not work either.

I have checked this URL:

http://www.devguru.com/Technologies/jetsql/quickref/operations.html

The operators that are listed are:
BETWEEN ... AND ...
INNER JOIN
LEFT JOIN
LIKE
RIGHT JOIN
UNION


=> nothing that could be like "Minus".

That's strange, isn'it ?
Is it possible to create a "Minus" operator with the operators above ?

Alex

 

[blom0344]

Hello Alex,

A possible work-around this situation could be running a delete query that fetches the primary key from the contents of table 2 and subsequently deletes matching records from table 1, thus giving you the sole records in table 1 with no matching ones to table 2.

DELETE table1.*
FROM table1
WHERE field1 IN
(SELECT field1 FROM table2)

where field1 is primary key of both tables.
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[halx]

That's a good idea. Thanks ! For what I need now, it works because I do not want to keep the original table1.

Alex