1 Тема от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Тема: Создание запроса и операции с датами

Имеется таблица people: первичный ключ id, поля: name, surname, fathername.
Таблица contacts содержит внешний ключ man_id от people, поля name (название контакта, например, сотовый), value (номер того же сотового) и index (тип enum yes/no - yes только 1 у 1 человека - приоритетный метод связи).
Таблица flg содержит внешний ключ man_id от people, и поле next_date (в формате date).
Таблица vaclist содержит первичный ключ id, поля: name (название прививки), period (тип date).
Таблица vac содержит внешние ключи man_id от people и vaclist_id от vaclist, поле date.
Таблица disp содержит первичный ключ id и внешний ключ man_id от people.
Таблица orders содержит внешний ключ disp_id от disp и поле done_date.
Вопрос такой: Как составить запрос, содержащий ФИО из таблицы people, приоритетный контакт (по полю index) из таблицы contacts, дату обращения для прохождения ФЛГ из таблицы flg, дату обращения для прохождения прививок (название прививки + дата, которая вычисляется добавлением к полю значению поля date таблицы vac значения поля period таблицы vaclist) и дату обращения для прохождения диспансерного осмотра (которая вычисляется добавлением к значению поля done_date 6 месяцев)?

2 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Сперва попробуйте объединить все таблицы. Если это сработает, то можно будет смотреть дальше.

SELECT *
FROM `people`
INNER JOIN (
`contacts`,
`flg`,
`vac`,
`vaclist`,
`disp`,
`orders`) ON (
`people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
AND `orders`.`disp_id` = `disp`.`id`
)

3 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

MySQL вернула пустой результат (т.е. ноль строк). ( запрос занял 0.0193 сек. )
SELECT *
FROM `people`
INNER JOIN (
`contacts` , `flg` , `vac` , `vaclist` , `disp` , `orders`
) ON ( `people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
AND `orders`.`disp_id` = `disp`.`id` )
LIMIT 0 , 30

4 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Тогда свяжите не все поля, чтобы проверить где проблемы, и дальше пробуйте подключать другие.

SELECT *
FROM `people`
INNER JOIN (
`contacts`,
`flg`,
`vac`,
`disp`
) ON (
`people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `people`.`id` = `disp`.`man_id`
)

5 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Отображает строки 0 - 3 (4 всего, запрос занял 0.0559 сек.)
SELECT *
FROM `people`
INNER JOIN (
`contacts` , `flg` , `vac` , `disp`
) ON ( `people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `people`.`id` = `disp`.`man_id` )
LIMIT 0 , 30

6 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Теперь добавьте поле `vaclist`. Если данные не будут выводиться, значит связи таблиц `vaclist` и `vac` нет, либо нет соответствующих данных в таблице `vaclist`.

SELECT *
FROM `people`
INNER JOIN (
`contacts`,
`flg`,
`vac`,
`vaclist`,
`disp`
) ON (
`people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
)

7 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Отображает строки 0 - 3 (4 всего, запрос занял 0.0107 сек.)
SELECT *
FROM `people`
INNER JOIN (
`contacts` , `flg` , `vac` , `vaclist` , `disp`
) ON ( `people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id` )
LIMIT 0 , 30

8 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Значит остается только связать поле `orders` и понять почему связь `orders`.`disp_id` = `disp`.`id` не возвращает данные.

SELECT *
FROM `people`
INNER JOIN (
`contacts`,
`flg`,
`vac`,
`vaclist`,
`disp`,
`orders`) ON (
`people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
AND `orders`.`disp_id` = `disp`.`id`
)

9 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

MySQL вернула пустой результат (т.е. ноль строк). ( запрос занял 0.0743 сек. )
SELECT *
FROM `people`
INNER JOIN (
`contacts` , `flg` , `vac` , `vaclist` , `disp` , `orders`
) ON ( `people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
AND `orders`.`disp_id` = `disp`.`id` )
LIMIT 0 , 30

По идее таблица orders должна заполняться, только если поле order таблицы disp имеет значение 'up'. Может с этим связано? Т.е. планировалось, что таблица disp отвечает за взятие на диспансерный учет (и снятие с него), а таблица order за посещение (явки в поликлинику).

10 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Doctor сказал:

По идее таблица orders должна заполняться, только если поле order таблицы disp имеет значение 'up'.

Если в таблице orders может не быть связанных данных, то в одном запросе будет сложно выбрать сразу все необходимые поля. Без таблицы orders получится примерно так:

SELECT
`people`.`name`,
`people`.`surname`,
`people`.`fathername`,
`contacts`.`name` AS `contact_name`,
`contacts`.`value` AS `contact_value`,
`flg`.`next_date`,
`vaclist`.`name` AS `vaclist_name`,
`vac`.`date` + `vaclist`.`period` AS `vac_date`
FROM `people`
INNER JOIN (
`contacts`,
`flg`,
`vac`,
`vaclist`,
`disp`
) ON (
`people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
)
WHERE `contacts`.`index` = "yes"

11 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Если есть возможность прикрепить изображение на форуме, могу поместить скрин схемы БД в дизайнере. Может это поможет? Если нет как создать второй запрос с данными таблиц people? contacts и orders?

12 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Doctor сказал:

могу поместить скрин схемы БД в дизайнере

Можете прикрепить через http://fastpic.ru/ например.

Но я уже пытался объяснить, что проблема здесь не в запросе, а скорее в данных.

Таблица disp содержит первичный ключ id и внешний ключ man_id от people.
Таблица orders содержит внешний ключ disp_id от disp и поле done_date.

Если у пользователя имеющего определенный man_id есть запись в таблице disp, но нет записи в связанной таблице orders, то выбирать нечего, поэтому самый первый запрос, который я указывал ничего не возвращает. Что же вы хотите выбрать из таблицы orders, если там нет данных которые можно было бы связать с таблицей disp?

13 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Схема связей и таблиц
http://i20.fastpic.ru/big/2011/0426/3d/d5929dc956aed5c1e9f51aefaaa0663d.jpg
Внес тестовые данные в таблицу orders и предложенный в самом начале запрос заработал smile

Отображает строки 0 - 3 (4 всего, запрос занял 0.0020 сек.)
SELECT *
FROM `people`
INNER JOIN (
`contacts` , `flg` , `vac` , `vaclist` , `disp` , `orders`
) ON ( `people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
AND `orders`.`disp_id` = `disp`.`id` )
LIMIT 0 , 30

Спасибо! А дальше как?

14 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Doctor сказал:

А дальше как?

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

15 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Немного подправил названия полей и получилось вот так

Отображает строки 0 - 1 (2 всего, запрос занял 0.0018 сек.)
SELECT `people`.`name` , `people`.`surname` , `people`.`fathername` , `contacts`.`name` AS `contact_name` , `contacts`.`value` AS `contact_value` , `flg`.`next_date` , `vaclist`.`vac_name` AS `vaclist_vac_name` , `vac`.`date` + `vaclist`.`period` AS `vac_date`
FROM `people`
INNER JOIN (
`contacts` , `flg` , `vac` , `vaclist` , `disp` , `orders`
) ON ( `people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
AND `orders`.`disp_id` = `disp`.`id` )
WHERE `contacts`.`index` = "yes"
LIMIT 0 , 30

16 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Doctor сказал:

Немного подправил названия полей и получилось вот так

Я же не могу выполнить запрос, чтобы его проверить, просто скажите работает он или нет. Если не работает, то чего не хватает.

17 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Работает

Отображает строки 0 - 1 (2 всего, запрос занял 0.0018 сек.)

18 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

А какие нужно использовать функции, чтобы добавить к значению поля done_date 6 месяцев, а к значению поля date таблицы vac значение соответствующего поля period из vaclist?

19 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Попробовал вот такой запрос:

SELECT "people"."name",
"people"."surname",
"people"."fathername",
"contacts"."name",
"contacts"."value",
"contacts"."index",
"flg"."next_date",
"orders"."done_date",
"vac"."date",
"vaclist"."period",
"vac"."next_place",
DateAdd( "m", 6, "orders"."done_date" ) AS "Следующая явка"
FROM "livesey"."orders" AS "orders",
"livesey"."disp" AS "disp",
"livesey"."contacts" AS "contacts",
"livesey"."people" AS "people",
"livesey"."flg" AS "flg",
"livesey"."vac" AS "vac",
"livesey"."vaclist" AS "vaclist"
WHERE "orders"."disp_id" = "disp"."id"
AND "contacts"."man_id" = "people"."id"
AND "disp"."man_id" = "people"."id"
AND "flg"."man_id" = "people"."id"
AND "vac"."man_id" = "people"."id"
AND "vac"."vaclist_id" = "vaclist"."id"
AND "contacts"."index" = 'yes'
AND "vac"."next_place" = ''

Пишет:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."name", "people"."surname", "people"."fathername", "contacts"."name", "cont' at line 1

Что не так и как правильно?

20 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Doctor сказал:

добавить к значению поля done_date 6 месяцев

DATE_ADD(`orders`.`done_date`, INTERVAL 6 MONTH) AS `done_date_plus`...

Doctor сказал:

к значению поля date таблицы vac значение соответствующего поля period из vaclist

Если поля имеют один тип, то достаточно их сложить.
`vac`.`date` + `vaclist`.`period` AS `vac_period`...

Doctor сказал:

Что не так и как правильно?

Двойные кавычки используются исключительно для строковых данных, названия таблиц и полей должны быть заключены в косые кавычки (`).

21 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

EXPLAIN SELECT `people`.`name` , `people`.`surname` , `people`.`fathername` , `contacts`.`name` AS `contact_name` , `contacts`.`value` AS `contact_value` , `flg`.`next_date` , `vaclist`.`vac_name` AS `vaclist_vac_name` , `vac`.`date` + `vaclist`.`period` AS `vac_date` , `vac`.`next_place` , DATE_ADD( `orders`.`done_date` , INTERVAL 6
MONTH ) AS `done_date_plus`
FROM `people`
INNER JOIN (
`contacts` , `flg` , `vac` , `vaclist` , `disp` , `orders`
) ON ( `people`.`id` = `contacts`.`man_id`
AND `people`.`id` = `flg`.`man_id`
AND `people`.`id` = `vac`.`man_id`
AND `vaclist`.`id` = `vac`.`vaclist_id`
AND `people`.`id` = `disp`.`man_id`
AND `orders`.`disp_id` = `disp`.`id` )
WHERE `contacts`.`index` = "yes"
AND `vac`.`next_place` = "поликлиника № 2"

Вот так получилось... Но сдается мне, что DATE_ADD я не туда разместил и из-за этого не могу создать Представление. Выводится ошибка:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXPLAIN SELECT `people`.`name` , `people`.`surname` , `people`.`fathername` , `c' at line 4

Как правильно?

22 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Doctor сказал:

Но сдается мне, что DATE_ADD я не туда разместил

Вроде все на месте. Попробуйте без EXPLAIN. Если не получится, но ошибка изменится, то покажите ее.

23 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Без EXPLAIN работает нормально. Спасибо.

24 Ответ от Doctor

  • Doctor
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2011-04-22
  • Сообщений: 18

Re: Создание запроса и операции с датами

Возникла такая проблема: в поле period планировалось хранить такие даты, как 10 лет или 1 месяц и т.д. Но как их записать? 00.00.10 не воспринимается... В как по другому - никак не придумаю sad. Посоветуйте, пожалуйста.

25 Ответ от Hanut

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,732

Re: Создание запроса и операции с датами

Doctor сказал:

в поле period планировалось хранить такие даты, как 10 лет или 1 месяц и т.д.

Если тип поля period - DATE, то в нем должна быть уже подсчитанная дата, соответственно, подсчитывать надо при занесении записи. Иначе, в поле следует хранить целое число, например, число месяцев и затем это число прибавлять к полю даты, таким образом подсчитывать можно прямо в запросе.