NOT EXISTS syntax problem

[pauld]

New at this :-)
2 tables
demographic visits
---------------- ------------------
custID | name | |custID | other stuff |
---------------- ----------------------

I want all those names where the custID doesnt appear in vists

select name FROM demographic WHERE NOT EXISTS (SELECT custID FROM
visits WHERE demographic.custID=visits.custID);

is a syntax error

[Janwillem Borleffs]

pauld wrote:

I want all those names where the custID doesnt appear in vists

select name FROM demographic WHERE NOT EXISTS (SELECT custID FROM
visits WHERE demographic.custID=visits.custID);

is a syntax error

Which version of MySQL are you using? This syntax is supported by MySQL
version 4.1 and up.
JW

[Andy Hassall]

On 29 May 2005 00:26:09 -0700, "pauld" <pd****@yahoo.co.uk> wrote:

New at this :-)

2 tables
demographic visits
---------------- ------------------
custID | name | |custID | other stuff |
---------------- ----------------------

I want all those names where the custID doesnt appear in vists

select name FROM demographic WHERE NOT EXISTS (SELECT custID FROM
visits WHERE demographic.custID=visits.custID);

is a syntax error

Not in most databases. Which one are you using? What's the actual error
message?

There are various ways of rewriting the query to produce the same result
(there's nothing wrong with your syntax on most databases though) - but it
depends which one you're using.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[p cooper]

Im not at work at the moment .....

gentoo linux server with probably 4.0.22

I suppose its possibe to rewrite it with a JOIN ( or perhaps a NOT JOIN ?)?

clues please.

[Malcolm Dew-Jones]

p cooper (pd********************@yahoo.co.uk) wrote:
: Im not at work at the moment .....

: gentoo linux server with probably 4.0.22

: I suppose its possibe to rewrite it with a JOIN ( or perhaps a NOT JOIN ?)?

: clues please.

left join the tables on the shared column, and look for rows where the
second table has null in the joined column. Those are the rows where the
right table doesn't exist.

--

This space not for rent.

[Jerry Stuckle]

pauld wrote:

New at this :-)
2 tables
demographic visits
---------------- ------------------
custID | name | |custID | other stuff |
---------------- ----------------------

I want all those names where the custID doesnt appear in vists

select name FROM demographic WHERE NOT EXISTS (SELECT custID FROM
visits WHERE demographic.custID=visits.custID);

is a syntax error

Pauld,

Depending on your MySQL version, try something like:

SELECT name FROM demographic WHERE custID NOT IN (SELECT DISTINCT custID
FROM visits;

(the DISTINCT just means do unnecessarily return duplicate values).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================