I got the following definition tables:

CREATE TABLE VENTAS (
CLIREAL INT NOT NULL,
MATREAL CHAR(10) NOT NULL,
QTY SMALLINT NOT NULL DEFAULT 0,
AMOUNT DECIMAL(9,2) NOT NULL DEFAULT '0.00',
MES SMALLINT NOT NULL,
CLIENTE INT NOT NULL DEFAULT 0,
MATERIAL INT NOT NULL DEFAULT 0
)

CREATE TABLE CLIENTCONV (
CODE INT PRIMARY KEY,
CONV INT NOT NULL DEFAULT 0
)

CREATE TABLE CLIENTES (
CODE INT PRIMARY KEY,
NAME VARCHAR(40) NOT NULL,
VENDEDOR TINYINT NOT NULL DEFAULT 0,
USUARIO INT DEFAULT 0xFFFFFFFF
)

I need to find the members that exists on the following select queries (not
union):

SELECT DISTINCT CLIREAL FROM VENTAS WHERE CLIREAL NOT IN (SELECT CODE FROM
CLIENTES)
SELECT DISTINCT CLIREAL FROM VENTAS WHERE CLIREAL NOT IN (SELECT CODE FROM
CLIENTCONV)

Basically I need to choose the rows VENTAS.CLIREAL that do not exists in
CLIENTES.CODE rows and that also do not exists in the CLIENTCONV.CODE rows.

thank's.

David Méndez.

David Méndez wrote:

select distinct clireal
from ventas
where clireal not in (select code from clientes)
and clireal not in (select code from clientconv)

Alternatively:

select distinct clireal
from ventas
where clireal not in (select code from clientes
union select code from clientconv)

Great!!!
thank you.

David.

"Ed Murphy" <emurphy42 (AT) socal (DOT) rr.com> wrote