Grab data WHERE table.ID NOT LIKE otherTable.ID

"Mark G. Franz" wrote:

Here is my SQL;

"SELECT Registration.CustomerID, Customer.CustomerID AS CustID, Firstname,
Lastname, Username, Password, Question, Answer, Email, Age, Gender, Address,
City, State, Zip FROM Customer, Registration WHERE Customer.CustomerID NOT
LIKE Registration.CustomerID"

I'm trying to return all the records from Customer that have not registered
in Registration, so the psuedo sql would read;

Return everything from all fields in Customer where the ID is NOT in the
Registration table. Any ideas?

Thanks,

Mark

Try the NOT EXISTS operator:

SELECT Customer.CustomerID AS CustID, Firstname, Lastname, Username,
Password, Question, Answer, Email, Age, Gender, Address, City, State,
Zip
FROM Customer
WHERE Customer.CustomerID
NOT EXISTS (SELECT *
FROM Registration
WHERE Registration.CustomerID = Customer.CustomerID);

Hope this helps,

Nils Zonneveld
--
Alles van waarde is weerloos
Lucebert

On Fri, 6 Jul 2001, Mark G. Franz wrote:

Here is my SQL;

"SELECT Registration.CustomerID, Customer.CustomerID AS CustID, Firstname,
Lastname, Username, Password, Question, Answer, Email, Age, Gender, Address,
City, State, Zip FROM Customer, Registration WHERE Customer.CustomerID NOT
LIKE Registration.CustomerID"

I'm trying to return all the records from Customer that have not registered
in Registration, so the psuedo sql would read;

Return everything from all fields in Customer where the ID is NOT in the
Registration table. Any ideas?

Maybe:
select ... from Customer where NOT EXISTS (Select * from
Registration where Customer.CustomerID LIKE Registration.CustomerID)

Yup! This is what I ended up using, (couldn't retract my email fast
enough...);

<% rs = st.executeQuery("SELECT DISTINCT CustomerID, Username, Password,
Email FROM Customer WHERE NOT EXISTS (SELECT Registration.CustomerID WHERE
(Registration.CustomerID = Customer.CustomerID)) ORDER BY CustomerID"); %>

Thanks!
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Mark G. Franz" <mgfranz@pe.net>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, July 10, 2001 4:10 PM
Subject: Re: [GENERAL] Grab data WHERE table.ID NOT LIKE otherTable.ID

On Fri, 6 Jul 2001, Mark G. Franz wrote:

Here is my SQL;

"SELECT Registration.CustomerID, Customer.CustomerID AS CustID,

Firstname,

Lastname, Username, Password, Question, Answer, Email, Age, Gender,

Address,

City, State, Zip FROM Customer, Registration WHERE Customer.CustomerID

NOT

LIKE Registration.CustomerID"

I'm trying to return all the records from Customer that have not

registered