CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nida
Nida

Reputation: 1702

Fetch corresponding States of Countries table

We have two tables Country and CountryStates with the following fields

Country
- CountryId
- CountryName

CountryStates
- StateId
- StateName
- CountryId

There are some Countries without any states added in the database. Now we have to fetch only those countries where states are added.

Upvotes: 0

Views: 439

Answers (4)

Shahzad Riaz
Shahzad Riaz

Reputation: 354

Same Result can be obtained by using sample INNER JOIN..

SELECT CountryID,CountryName
FROM Country C
INNER JOIN CountryStates S
ON C.CountryID = S.CountryID

Upvotes: 1

luke_t
luke_t

Reputation: 2985

You can perform a JOIN (also known as INNER JOIN) on CountryStates, using the CountryId.

This will leave you with only the details of Countries which have a state added for them.

SELECT *
FROM Country
INNER JOIN CountryStates ON Country.CountryId = CountryStates.CountryId

Upvotes: 1

Horia
Horia

Reputation: 1612

You can use this query to retrieve only countries that have a state:

SELECT *
FROM    Country AS C
WHERE   EXISTS (SELECT TOP 1 1 
                FROM    CountryStates CS
                WHERE   CS.CountryId = C.CountryId
            )

Upvotes: 1

ughai
ughai

Reputation: 9890

Use Exists like this

SELECT CountryID,CountryName 
FROM Country C
WHERE EXISTS
(
    SELECT 1 
    FROM CountryStates S 
    WHERE S.CountryID = C.CountryID
)

Upvotes: 1

Related Questions