Simple SQL Query - Help

[Hysteria862]

Hi,
I'm just wondering if this statement is logically possible in SQL. I'm writing this example in semi-pseudo code.

I have two tables:
persons (a group of people, each has an id)
travelling persons (the specific members of the group travelling on a particular journey, again with their id, if they are not travelling they have a field 'deleted' set to 'Y'.

Basically I want to retrieve results where:

persons.id IS NOT EQUAL TO travelling_persons.id (WHOSE DELETED FIELD IS SET TO 'Y')

[Fumigator]

There are two options-- the first is to do a subquery:


SELECT p.*
FROM persons as p
WHERE NOT EXISTS (
SELECT 1
FROM travelling_persons as tp
WHERE p.persons_id = tp.travelling_persons_id
AND tp.deleted = 'Y')


Or a LEFT JOIN, checking the right-hand table for NULL (meaning the join didn't have a match):


SELECT p.*
FROM persons as p
LEFT JOIN travelling_persons as tp
ON p.persons_id = tp.travelling_persons_id
AND tp.deleted = 'Y'
WHERE tp.travelling_persons_id IS NULL