Обсуждение: Grab data WHERE table.ID NOT LIKE otherTable.ID

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

"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
> > 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)
>