Country which has no old country

Databases
1 
id 	country 	year
1 	America 	1776
2 	Korea 	       1945
2 	Koryo_old 	918
3 	Qing_old 	1616
3 	China 	        1949
4 	Germany 	1949
2    	Koguryo_old 	34
5 	AngkorWat_old 	802

I have data like the above.
The code1 below produces the result1 below.

[b]code1[/b]
select id,country from test
where right(country,4)<>'_old'
order by year
[b]result1[/b]
1 America
2 Korea
3 China
4 Germany

I like to produces the countries which have no old name(_old)./*all old countries have “_old” at the end.
my target result will be the below.

[b]target result[/b]
1. America
4. Germany

How can I get my target result above?

2

use a NOT EXISTS subquery in your current query

alternatively, do a LEFT OUTER self-join with an IS NULL test

3

Should I use parenthesis for creating subquery?

4

what happened when you tested it? ™

:slight_smile:

5 
select id,country from test
where NOT EXISTS
(SELECT * 
FROM test
WHERE right(country,4)<>'_old')

The code above does produce nothing.

6

i wonder if you have taken any time to try to figure out why?

no? okay, i will help you

run the subquery by itself, and see if it returns any rows

yes, it returns rows

so the NOT EXISTS condition will be false, and therefore the outer query will return no rows

you need to rewrite the subquery in accordance with your specification from post #1 – “which have no old name”

the NOT EXISTS has to check for an old name that matches the row it’s being evaluated for

thus, it has to be a correlated subquery (if you don’t know what that means, please do some googling)

7

I have some time to figure out why, but not much time.

I am waiting for it.

I am not sure what “the subquery by itself” means.

Yes, I am usually doing it.

In the case of above, it produces nothing.
I am not sure “NOT EXISTS” is for what?
what I am sure is that the code above does not produce any sql error.

I wrote “right(country,4)<>'_old” for it.

I can find some poor explanation for it. I am waiting your proper explanation and the code.

8

run this –

SELECT * 
FROM test
WHERE right(country,4)<>'_old'

does it produce any results? if yes, then the entire query returns nothing

it’s used to make sure a subquery returns no rows

i have been training you and teaching you and helping you for YEARS

i am not going to just hand you a solution any more

it is time you figured out sql for yourself