SQL syntax for 'not exists' or 'not in' .... Advice?

Classic Servoy
1

I am stumped trying to find a way forward in compiling an SQL query which involves a ‘not’ in a related table. I have trawled this forum and internet sites relating to SQL syntax with little success so far.

I have a main table ‘people’ and many related tables which contain criteria such as languages spoken; locations where they have lived; qualifications they hold and so on.

I can easily generate queries such as: select id_person from people inner join on people.id_person = languages.id_person where languages.language = ‘dutch’ or languages.language = ‘french’. Such ‘positive associations’ are easy to handle.

When I need to find people who have certain languages but not another, I need to avoid constructs such as: languages.language = ‘dutch’ or languages.language = ‘french’ and languages.language != ‘german’. This is because the foundset would contain a person’s record if that person spoke both dutch and german. It would also contain records of people who spoke languages other than german, whether or not they spoke dutch or french.

My requirement is to exclude from the foundset all people who do not speak german, and limit the foundset to those who speak dutch or french (in this simple example).

Has anyone a clue to offer on how one deals with this scenario? Any worked examples in SQL tutorials on the net for example?

Richard

Servoy Developer
Version 3.5.6-build 519
Java version 10.0-b19 (Windows XP)

2

doesn’t ```
!=

3

In plain SQL this would be :

NOT languages.language = 'german'

Hope this helps.

4

Reading your question a little better you might have to use another method.
If languages is another related table then contacts that don’t speak german probably don’t have a language record with the value ‘german’ anyway or even any language record at all. So you can’t search this way on data that isn’t there also the inner join would filter out those ‘non-language’ contacts.
You should use the following syntax instead:

SELECT id_person FROM people WHERE id_person IN 
(
	SELECT id_person FROM languages WHERE language IN ('dutch','french') 
	AND id_person NOT IN 
	(
		SELECT id_person FROM languages WHERE language = 'german'
	)
);

Indentations are used to clarify the structure and can be left out.

Hope this helps.

Edit: Fixed the SQL, it contained 1 bracket to much

5

Marcel and Robert

Thanks for your quick responses. Unfortunately it is rather more complicated than that!

languages.language != ‘german’ retrieves all people who have a language that is not ‘german’. For example, a person who speaks ‘arabic’ and ‘german’ will be returned, because ‘arabic’ != german. Most of the people in the database speak several languages, so such records are a common occurrence.

What I need to do is to construct the query such that if the person speaks ‘german’, her/his pk is not included in the foundset, even though the person also speaks a language that is not ‘german’.

The foundset should comprise records for people whose set of languages does not include ‘german’ (in this simple example).

Does this make sense?

Similar considerations apply to “not languages.language = ‘german’”.

It seems that I may need to use a sub-select of sorts, or a subsequent ‘where not exists…’ or ‘where not in…’. But I have had no success so far.

6

richh:
What I need to do is to construct the query such that if the person speaks ‘german’, her/his pk is not included in the foundset, even though the person also speaks a language that is not ‘german’.

That’s what my second post does.

7

Robert - you are wonderful!

Your code works well:

SELECT id_person FROM people WHERE id_person IN
(
   SELECT id_person FROM languages WHERE language IN ('dutch','french')
   AND id_person NOT IN
   (
      SELECT id_person FROM languages WHERE language = 'german'
   )
);

Looking at the construction of your query I tried:

SELECT id_person FROM people WHERE id_person IN
(
   SELECT id_person FROM languages WHERE language IN ('dutch','french')
)
 AND id_person NOT IN
(
   SELECT id_person FROM languages WHERE language = 'german'
);

This second form of the query appears to produce the same foundset as the first form.

Is there any particular reason why you would perfer the first form of the query rather than the second?

8

Both work but I think your query is even better for the parser :)
So use your version instead.

9

SELECT id_person FROM people

left outer join languages as lang_german on (
people.id_person = lang_german.id_person
and lang_german.language = ‘german’
)

where lang_german.id_language is null

gets you all people that don’t speak german,
you can make separate joins for each language.
should be a lot faster on large tables than using subqueries

Hope this helps, Hans Nieuwenhuijs, Ayton