Запрос на выборку с трех таблиц. Кто профи?)

@Jallik Bleach · Регистрация: 16.03.2013

Author24 — интернет-сервис помощи студентам

Здравствуйте, форумчане.
Вот такая возникла задача: база данных аеропорта. Три таблицы: countries (id, country), ports (id, country_id, name) и lines (id, p_from_id, p_to_id, out_date....)

Кликните здесь для просмотра всего текста

countries

id	country
1	Россия
2	Украина

ports

id	country_id	name
1	1	Москва
2	1	Екатеринбург
3	2	Киев

lines

id	p_from_id	p_to_id	out_date	...
1	1	2	2013-06-08 18:00:00	...
2	1	3	2013-06-08 18:00:00	...
3	3	2	2013-06-08 18:00:00	...

1. Необходимо в результате запроса получить таблицу со странами вылета и прилета

Кликните здесь для просмотра всего текста

p_from_id (country)	f_to_id (country)
Россия	Россия
Россия	Украина
Украина	Россия

P. S. Если пара совпадает (например Москва - Киев, Екатиренбург - Киев), то в таблице-результате должна быть одна строка Россия - Украина

2. В результате запроса получить таблицу с портами вылета и прилета

Кликните здесь для просмотра всего текста

p_from_id (port)	p_to_id (port)
Москва	Екатиренбург
Москва	Киев
Киев	Екатиренбург

P. S. Здесь совпадения заменять не надо (они могут отличаться временем вылета, стоимостью билета и т. п.)

Может я неправильно спроектировал таблицы, поэтому у меня все так сложно... Помоги пожалуйста.

P. S. Не забываю нажать "Спасибо"

Добавлено через 1 час 54 минуты
Второй вопрос получилось решить

Во первых, таблицу lines перейменовал в avialines (походу ключевое слово)

Запрос:

SQL

SELECT avialines.*, p_from.name AS from_name, p_to.name AS to_name 
        FROM avialines
    LEFT JOIN ports AS p_from ON p_from.id=avialines.p_from_id
    LEFT JOIN ports AS p_to ON p_to.id=avialines.p_to_id

@cygapb-007 · 14.06.2013, 00:54

T-SQL

select distinct cf.country, ct.country
from lines l
join 
  ports pf 
  join countries cf on cf.id=pf.country_id
  on pf.id=l.p_from_id
join
  ports pt
  join countries ct on ct.id=pt.country_id
  on pt.id=l.p_to_id;

T-SQL

;
with country as (
  select p.id, c.country
  from ports p
  join countries c on c.id=p.contry_id )
select f.country,t.country
from lines l
join country f on f.id=l.p_from_id
join country t on t.id=l.p_to_id
group by f.country,t.country;

T-SQL

select 
  (select /*top(1)*/ country from countries where id=cif) [from],
  (select /*top(1)*/ country from countries where id=cit) [to]
from (
   select f.country_id cif, p.country_id cit
   from lines l
   join ports f on f.id=l.p_from_id
   join ports t on t.id=l.p_to_id
   group by f.country_id, p.country_id
   ) c

	14.06.2013, 00:54