Country which is NOT in reading

Databases
1

code sender country
(12) Jane France
(12) Jane Spain
(15) Andy Germany
(15) Mary Italy
(16) Tom Spain[/code]I have a table named “sending” like the above and another table named reading like the below.

(read_day) reader country (11) Andy Germany (12) Jane Italy (14) Andy France (16) Tom GermanyWhen I like to find matching country in the table “reading”, I can use the code below.

SELECT reading.country as country FROM reading LEFT JOIN sending ON reading.country=sending.country WHERE reading.country is NOT null GROUP BY countryThe code above produces the result below.

France Germany ItalySo far so good.

Now I like to find countries which are not in the table “reading”.
I made the trial code below for it.

SELECT reading.country as country FROM reading LEFT JOIN sending ON reading.country=sending.country WHERE reading.country is null GROUP BY country My target result is the following, but the code above produces nothing.

Spain

Why does the trial code above produce nothing?
How can I get my target result above?

2

because there is no row in reading where the country is null

1 Like
3

Would it need to be something along the lines of

SELECT reading.country from reading where reading.country not in (select distinct sending.country as scountry from sending)
4

In what table are these countries?

5

sending, as I read it.

6

joon has merely forgotten how left joins work and which table is supposed to be on the left

i wonder if there’s a language problem in interpreting our replies/suggestions?

7

SELECT sending.country as country FROM sending LEFT JOIN reading ON reading.country=sending.country WHERE reading.country is null GROUP BY countryI’ve got my target result with the code above.

1 Like
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.