By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,406 Members | 1,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,406 IT Pros & Developers. It's quick & easy.

NOT EXISTS syntax problem

P: n/a
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

Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Jul 17 '05 #2

P: n/a
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
Jul 17 '05 #3

P: n/a
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.

Jul 17 '05 #4

P: n/a
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.
Jul 17 '05 #5

P: n/a
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
==================
Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.