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.cus tID=visits.cust ID);

is a syntax error

[Janwillem Borleffs]

Re: NOT EXISTS syntax problem

pauld wrote:[color=blue]
> 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.cus tID=visits.cust ID);
>
> is a syntax error[/color]

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


JW

[Andy Hassall]

Re: NOT EXISTS syntax problem

On 29 May 2005 00:26:09 -0700, "pauld" <pdc124@yahoo.c o.uk> wrote:
[color=blue]
>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.cus tID=visits.cust ID);
>
>is a syntax error[/color]

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 / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[p cooper]

Re: NOT EXISTS syntax problem

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]

Re: NOT EXISTS syntax problem

p cooper (pdconetwofour_ numbers_@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]

Re: NOT EXISTS syntax problem

pauld wrote:[color=blue]
> 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.cus tID=visits.cust ID);
>
> is a syntax error
>[/color]

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.
jstucklex@attgl obal.net
=============== ===