exist sql с подзапросом + where and mysql +

Primary tabs

vedro-compota's picture
Submitted by vedro-compota on Sun, 12/18/2011 - 23:43

Forums:

Немного изменим условие и схему базы данных для предыдущей задачи -пусть у нас есть 4-ре таблицы =

  • 1) entity - сущность содержит помимо прочего порядковый номер фильма (id) , цену за сутки проката и сумму залога - ну и конечно порядковый номер сущности.
  • 2) film - это таблица содержит всевозможные данные о фильме - в том числе порядковый номер фильма.
  • 3) nositel - хранит информацию, о носителе, на котором записан фильм (cd или dvd , например)
  • 4) vidacha - эта таблица хранит информацию о выдаче и возврате дисков - также здесь храниться id носителя

скрипт разворота = 

create table Film 
(id_filma VARCHAR(200),
 Nazvanie VARCHAR(200),
 Zhanr VARCHAR(200),
 Rezhiser VARCHAR(200),
 Spisok_aktorov VARCHAR(2000));


create table Entity 
(id_id_filma VARCHAR(200) Not Null REFERENCES Film(id_filma),
 id_id_nositelya VARCHAR(200) NOT NULL REFERENCES Nositel(id_nositelya), tsena_za_sutku varchar(20) Not Null,
 summa_zaloga VARCHAR(200) Not Null);


create table Vidacha 
(id_nositelya VARCHAR(200) Not Null,
Data_vidachi date Not Null,
Familya VARCHAR(200) Not Null,
Imya VARCHAR(200) Not Null, 
Otchestvo VARCHAR(200) Not Null,primary key (id_nositelya),
Data_vozvrata date);


create table Nositel
 (id_nositelya VARCHAR(200),
 id_filma VARCHAR(200),
 tip_nositelya VARCHAR(200),
 primary key (id_nositelya));

от нас требуется -

написать запрос, который вывел бы названия всех фильмов, записанных на CD-диски, которые взяли и на данный момент не вернули


попробуем.

Главная идея предлагаемого решения следующая -

В записи, произведённой для выданного фильма , который ещё не вернули будет присутствовать только дата выдачи, а даты возврата не будет .

На языке SQL это значит, что дата возврата будет равна NULL

Здесь неизбежно придётся соединять таблицы .

Итак , вот что у меня получается (общая идея)= 

select nazvanie from ( film left join nositel on film.id_filma=nositel.id_filma) as NEWTABLE where 
 ("CD" = select nositel.tip_nositelya from nositel where nositel.id nositelya =  NEWTABLE.id_nositelya) 
and
(exists (select vidacha.id_nositelya  from vidacha where (vidacha.id_nositelya = NEWTABLE.id_nositelya )
 and (vidacha.data_vozvrata is NULL) );


____________________________________

ПРИМЕЧАНИЕ =

предложенная выше логика решения (отсутствие дополнительной проверки не равенства NULL даты самой выдачи) , основывется на том предположении, что если для фильма делается некая запись в таблице выдачи - vidacha , то дата самой выдачи там неизбежно будет присутствовать, чего нельзя сказать о дате возврата.

____________________________

Источники(почитать ещё) =

_____________________________________________
Ключевые слова и фразы(для поиска)=
left join alias where mysql
left join назначить псевдоним полученной таблице
alias for left join result
left join подзапрос mysql

  • Log in to post comments
  • 7850 reads
vedro-compota's picture

vedro-compota

Mon, 12/26/2011 - 00:17

в MySQL можно попробовать так =

SELECT Film.nazvanie
  FROM Film,Nositel,Vidacha
  WHERE Film.id_filma=Nositel.id_filma AND Nositel.tip_nositelya='CD' AND
        Nositel.id_nositelya=Vidacha.id_nositelya AND Vidacha.Data_vozvrata IS NULL

______________________________

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

vedro-compota

Mon, 12/26/2011 - 00:21

Это решение с использованием прямого (или декартового ) произведения множеств строк трёх таблиц, но с добавленным ограничением WHERE , в котором требуется, чтобы (в том же порядке , что и в запросе ниже) =

  1. диск соответствовал фильму
  2. диск был типа CD
  3. диск значился в списке выданных
  4. запись о возврате диска отсутствовала (IS NULL)
SELECT Film.nazvanie
  FROM Film,Nositel,Vidacha
  WHERE Film.id_filma=Nositel.id_filma AND Nositel.tip_nositelya='CD' AND
        Nositel.id_nositelya=Vidacha.id_nositelya AND Vidacha.Data_vozvrata IS NULL

__________________________

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

vedro-compota

Mon, 12/26/2011 - 00:45

кстати, если вернуться к left join , то запрос надо формировать следующим образом - 

select nazvanie from (SELECT <список полей> 
FROM film left join nositel on film.id_filma=nositel.id_filma WHERE ...) as NEWTABLE ...

______________

но об этом завтра.....чуть позже....

_____________
матфак вгу и остальная классика =)