Web Development

Hi all,

I have an interesting and I am sure simple question for yau'll.

Basically this is what I am after:

The table:
CREATE TABLE places (id INT, city VARCHAR(24), name VARCHAR(24));

The data:
id = 1, city = canberra, name = aaron
id = 2, city = canberra, name = andrew
id = 3, city = canberra, name = john
id = 4, city = perth, name = grahame
id = 5, city = perth, name = alex
id = 6, city = perth, name = bob

The result:
canberra

Why: ?!?!?
I want to return the distinct values of "city", but, not if there is a person in that city named "bob".

Help would be appreciated.

Thanks,

What query have you tried?

Solved:

SELECT DISTINCT(x.city) FROM places x WHERE NOT EXISTS (SELECT 1 FROM places y WHERE y.name = 'bob' AND x.city = y.city);

Out of curiosity, what would be the results of:

Code:

SELECT DISTINCT(city) FROM places WHERE name <> "bob"

Does "perth" show up?

Those are the lines I was originally thinking along... but,

That will simply display the distinct city values, and only not show ones where "bob" is the only name associated.

I have now verified this with a test DB.