CODEWITHSUNDEEP
sqlsql-serverleft-join
Eels Fan
Eels Fan

Reputation: 2063

Querying with an outer join in SQL

I have a SQL database that contains geographic information. This database has three tables:

PostalCode
----------
Code  (char(10))
StateID (uniqueidentifier)

State
-----
ID (uniqueidentifier)
Name (nvarchar(max))
CountryID (uniqueidentifier)

Country
-------
ID (uniqueidentifier)
Name

The relationship is: A country has states. States have postal codes. I'm trying to create a query where I can find all of the states in the country where a specific postal code belongs. Currently, I'm trying the following:

SELECT 
  s.*
FROM 
  [PostalCode] p, 
  [State] s,
  [Country] c
WHERE
  p.[Zip]='90028' AND 
  p.[StateID]=s.[ID] AND 
  s.[CountryID]=c.[ID]

Unfortunately, this result returns 1 record (The State record associated with California). However, in reality, I need it to return 50 records (one for each state in the united states). How do I modify this query to do this?

Thank you

Upvotes: 2

Views: 98

Answers (1)

Taryn
Taryn

Reputation: 247870

You are using an INNER JOIN, you need to change your syntax to a LEFT JOIN:

SELECT  s.*
FROM [State] s
LEFT JOIN [PostalCode] p
  ON p.[StateID]=s.[ID]
  AND p.[Zip]='90028'
LEFT JOIN [Country] c
  ON s.[CountryID]=c.[ID]

You will notice that I changed to use ANSI JOIN syntax instead of the joining the tables with the commas and the WHERE clause.

A LEFT JOIN will return all rows from the state table even if there are not matching rows in the other tables.

If you want to return all states in a country where the postal code is equal to a specific code, then you can use:

select s.*
from state s
inner join
(
  SELECT s.countryid
  FROM [State] s
  INNER JOIN [PostalCode] p
    ON p.[StateID]=s.[ID]
  INNER JOIN [Country] c
    ON s.[CountryID]=c.[ID]
  WHERE p.[Zip]='90028'
) c
  on s.countryid = c.countryid;

Or you can use:

select s1.*
from state s1
where exists (select s2.countryid
              from state s2
              inner join country c
                on s2.countryid = c.id
              inner join postalcode p
                on s2.id = p.stateid
              where p.zip = 90028
               and s1.countryid = s2.countryid)

Upvotes: 3

Related Questions