I have been trying to work out a solution to the following problem without any success.
Are there any experts out there that can help?

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

Are you having problems with the subquery or the join or both?

Do you understand how to write the solution in general terms even if you don't know how to write the subquery or join?

Both are pretty basic.

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

can you give an example of what you have tried for coding this? The left outer join gives null values for non matching values which you could use to get the cities in question.

select distinct city.name from city,country
where city.name <> country.capital;

returns all the 18 cities in the city table
I thought including where city.name i<> country.capital should logically work but it doesn't


select distinct city.name from city,country
where not exists (select capital from country);

gives a blank table

To create a left join when you use InfoMaker to design your DataWindow:

1) pick the two tables
2) Double click the equal sign on the link that joins them
3) Pick the correct relationship (rows that match, and any rows with no match from whichever table is the "parent")

Once you have the left join, you'll have to test for NULL values in the "child" table's primary key.

To do it using a subquery, use the NOT EXISTS operator in the operator column for the WHERE clauses.

-PatP

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

Are you using InfoMaker, or trying to write the SQL by hand? That makes a considerable difference in how you approach the project.

-PatP

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.

Glad you got the answer Gerald. If you apply this same principle in future cases you should be okay.

Also remember you can do multiple joins including inner and outer in the same query should you be joining more than two tables.

If it seems like people were trying to steer you towards an answer yourself rather than answering directly, that was the case. Have a quick read over this thread to see why:

http://www.dbforums.com/t997373.html