Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15

    Unanswered: excluding colums

    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

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    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.

  3. #3
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15

    problems

    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

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    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.

  5. #5
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15
    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

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you using InfoMaker, or trying to write the SQL by hand? That makes a considerable difference in how you approach the project.

    -PatP

  9. #9
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15

    SQL problem

    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.

  10. #10
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15
    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.

  11. #11
    Join Date
    Mar 2004
    Posts
    480
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •