Запрос с внешним соединением в SQL

У меня есть база данных SQL, содержащая географическую информацию. Эта база данных имеет три таблицы:

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

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

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

Отношения таковы: в стране есть штаты. Штаты имеют почтовые индексы. Я пытаюсь создать запрос, в котором я могу найти все штаты в стране, которым принадлежит определенный почтовый индекс. В настоящее время я пытаюсь сделать следующее:

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

К сожалению, этот результат возвращает 1 запись (запись штата, связанная с Калифорнией). Однако на самом деле мне нужно, чтобы он возвращал 50 записей (по одной для каждого штата в США). Как изменить этот запрос, чтобы сделать это?

Спасибо


sql sql-server left-join
person Eels Fan    schedule 15.03.2013    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Вы используете INNER JOIN, вам нужно изменить синтаксис на 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]

Вы заметите, что я изменил синтаксис на использование ANSI JOIN вместо объединения таблиц запятыми и предложением WHERE.

LEFT JOIN вернет все строки из таблицы state, даже если в других таблицах нет соответствующих строк.

Если вы хотите вернуть все штаты в стране, где почтовый индекс равен определенному коду, вы можете использовать:

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;

Или вы можете использовать:

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)
person Taryn    schedule 15.03.2013
comment
Это дает мне все штаты во всех странах, хотя - person Eels Fan; 15.03.2013