may be simple to some people but not to me.
whatever I do I seem to get all the rows in the city table
I want only the cities that are not in the country table
ps I am using Infomaker with SQL Anywhere
If anyone is following this thread, I have now found a solution to part (i) of the problem Write an SQL query using a subquery
SELECT DISTINCT name FROM city WHERE name NOT IN (SELECT capital FROM country)
I am still having a problem with part (ii) Write an SQL query using a left outer join
I cannot see how a left outer join can be used to exclude records
------------------------------------------------------------------------ Problem Details
List the name of each city in the city table that is not shown as the capital of a country in the country table
Write an SQL query to satisfy this request using:
a) a subquery
b) a left outer join
The significant columns in the country table are name -- name of the country capital - capital of that country.
The significant columns in the city table are name -- name of the city country --- the country where that city is located
Last edited by geraldisaacs; 05-17-04 at 20:25.
Reason: give more details
I am trying to write an SQL expression that will meet the requirements of the request specification and comply with Entry level SQL:1992 (ISO 9075:1992) as applied to Sybase SQL Anywhere version 5 and later.
This is not a practical problem of producing a User View, if it were I know of many ways to overcome it.
I have only recently started studying SQL in depth and I am not yet familiar with what can or can't be done. The question is part of a training program that I am taking part in and I really want to learn more from people who have years of practical experience with SQL.
I am coming to the conclusion that the answer to the question is that a left outer join is not an option for this problem. As I see it a left outer join combines two tables including all the rows in the table that is the first entity in the join condition
i.e. If the join condition was city LEFT OUTER JOIN country
For example the query SELECT DISTINCT city.name FROM
city LEFT OUTER JOIN country ON country.capital <> city.name;
then the resulting table would consist simply of the rows in the city table because the names of the capitals in country table also are present in the city table as names of cities.
I now have the solution to my problem SELECT a.name FROM city a LEFT OUTER JOIN country b ON a.name = b.capital WHERE b.name IS NULL;
I was not familiar with the IS NULL predicate and particularly how it can be used in this way.
Thank you to all who have helped me to find the solutions to these problems.