SQL multitasking query

Question

SQL multitasking query

Navigation

#1 by (1 votes)

1

I'm using the world database in SQL server 2016, and I have a little doubt, I'm practicing and one of the questions is to select the countries and their respective capitals, my problem is that in cities I have 5 or 6 country, and I do not know how to make me select only the capital.

    select c.Name, p.Name, c.Code from country as c
    inner join city as p on c.Code = p.CountryCode

is what has occurred to me so far, I appreciate your suggestions.

The tables are structured like this:

    CREATE TABLE city (
    ID int NOT NULL,
    Name char(35) NOT NULL DEFAULT '',
    CountryCode char(3) NOT NULL DEFAULT '',
    District char(20) NOT NULL DEFAULT '',
    Population int NOT NULL DEFAULT '0',
    PRIMARY KEY (ID)
    )

    CREATE TABLE country (
    Code char(3) NOT NULL DEFAULT '',
    Name char(52) NOT NULL DEFAULT '',
    Continent varchar(20) CHECK(Continent in ('Asia','Europe','North 
    America','Africa','Oceania','Antarctica','South America')) NOT NULL 
    DEFAULT 'Asia',
    Region char(26) NOT NULL DEFAULT '',
    SurfaceArea decimal(10,2) NOT NULL DEFAULT '0.00',
    IndepYear smallint DEFAULT NULL,
    Population int NOT NULL DEFAULT '0',
    LifeExpectancy decimal(3,1) DEFAULT NULL,
    GNP decimal(10,2) DEFAULT NULL,
    GNPOld decimal(10,2) DEFAULT NULL,
    LocalName char(45) NOT NULL DEFAULT '',
    GovernmentForm char(45) NOT NULL DEFAULT '',
    HeadOfState char(60) DEFAULT NULL,
    Capital int DEFAULT NULL,
    Code2 char(2) NOT NULL DEFAULT '',
    PRIMARY KEY (Code)
    )

sql

asked by Luis Felipe Salazar Nuñez 09.03.2018 в 16:54

source

1 answer

Generate Ordered Records of Different Tables problem loading 2 select with data in Laravel 5.6.7?

score 1 · Accepted Answer

If the first record you save must be the capital in your table country I think it's a bad statement, you have a field called Capital int DEFAULT NULL that by default is saved as null, you should save 1 since it is int, when register country as capital, so the condition WHERE would be like this in your query:

SELECT c.Name, p.Name, c.Code 
   FROM country as c
      INNER JOIN city as p ON c.Code = p.CountryCode
          WHERE c.capital != null

in the option that I suggested in the comments:

SELECT a.Name as pais, b.Name as capital 
   FROM city a 
     INNER JOIN contry b on b.Code = a.CountryCode
         WHERE b.capital != null

I hope it serves you!