Как правильно написать SQL запрос с под-select'ом?

Question

[DemonIa]

Здравствуйте,

Есть таблица tasks
id | task_name | task_created
-------------------------------------------------
1 | Принести воды | 15-09-2017
2 | Кормить котов | 14-09-2017
3 | Почесать собак| 12-09-2017
4 | Занять деньги | 15-09-2017

(две задачи созданные 15-го числа)

И таблица uploads
id | image_path | timestamp
-----------------------------------------------
1 | cat.jpg | 05-09-2017
2 | dogs.jpg | 15-09-2017
3 | bird.jpg | 12-09-2017
4 | star.jpg | 15-09-2017

(две загрузки созданные 15-го числа)

Чего я хочу: я хочу получить все записи с таблицы tasks, а также image_path из таблицы uploads, у которого uploads.timestamp совпадает с task.task_created. Если задача была создана 10-09-2017, а в uploads нет записей с этой датой - в столбце справой таблицы (поле uploads.timestamp) хочу получить null.
Важно: для одной задачи надо получить один путь к картинке с условием типа LIMIT 1 ORDER BY id ASC

Я написал что-то похожее:

SELECT task_name, image_path FROM tasks left join uploads on (uploads.timestamp = tasks.task_created)

, но в выборке дублируются результаты, и я думаю, что это надо писать не через join, а с вложенным select.

Буду очень благодарен за помощь!

UPDATE: Всем огромное спасибо. Вы, безусловно правы о внешних ключах и о неправильной логике выборки, но тонкость в том, что к одной задаче может быть "привязано" несколько картинок, причем задачи созданные на протяжении дня могут иметь одну и ту же картинку.
Именно сейчас мне надо получить список всех задач, и по одной картинке к задаче некого дня, а другим запросом, для другой страницы я буду доставать пути ко всем картинкам текущего дня, и крутить их в слайдере.
Логика нулевая, но кто платит - тот заказывает музыку.

Comments

[Denis Holub]

Неоднозначная выборка. Проще добавить во вторую таблицу поле fk_id на первую.
А так, что-то типа:

SELECT task_name, image_path FROM tasks left join uploads on (uploads.timestamp = tasks.task_created and  uploads.id <= tasks.id+1 and uploads.id >= tasks.id)

Answer 1

[PrAw]

SELECT task_name, 
  (SELECT image_path FROM uploads 
       WHERE uploads.timestamp = tasks.task_created 
        LIMIT 1) as im_path
FROM uploads

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

Answer 2

[Rsa97]

Ваша ошибка - привязка к timestamp, который не является уникальным. Привязываться надо к уникальному id задачи, то есть таблица uploads должна иметь вид (id, image_path, task_id).
На существующих данных вы либо будете получать декартово произведение таблиц, либо одна картинка, например dogs.jpg привяжется к двум разным задачам.

Answer 3

[Дмитрий Энтелис]

Ваше желание обусловлено неправильно спроектированной базой.
Почитайте про 3ю нормальную форму таблиц.

В данном случае нужно:

a) есть у task строго 1 или 0 картинок, картинки и бывают только в tasks:
добавить в uploads поле task_id

b) иначе:
завести новую таблицу xref_task_upload вида (task_id, upload_id)