Got following Tables in my SQL-Database (simplified):
Table Blogs:
+----+----------------------+----------+ | ID | Date | TitleGer | +----+----------------------+----------+ | 1 | 2017-04-28 15:09:46 | Huhu | | 2 | 2017-04-28 15:16:18 | Miau | | 3 | 2017-04-28 15:17:14 | Kleff | +----+----------------------+----------+
Table PicturesJoin:
+-------------+---------+---------------------+ | IDPicture | IDBlog | Date | +-------------+---------+---------------------+ | 86 | 1 | 2017-06-28 17:41:11 | | 87 | 1 | 2017-06-28 17:41:11 | +-------------+---------+---------------------+
Table Pictures:
+------+-------------------------+---------------------+ | ID | Filename | Date | +------+-------------------------+---------------------+ | 86 | 20170512200326_320.jpg | 2017-05-12 20:03:26 | | 87 | 20170512200326_384.jpg | 2017-05-12 20:03:30 | +------+-------------------------+---------------------+
PictureJoin “joins” the Picture with the Blog-Table. Now I use following SQL-Command to joine these two Tables (Blog – PictureJoin) / (PictureJoin – Pictures) together.
SELECT Blogs.ID, Blogs.Date, TitleGer, Pictures.Filename FROM Blogs LEFT JOIN PicturesJoin ON PicturesJoin.IDBlog = Blogs.ID LEFT JOIN Pictures ON Pictures.ID = PicturesJoin.IDPicture ORDER BY DATE DESC
The result might look like that:
+------+----------------------+-----------+------------------------+ | ID | Date | TitleGer | Filename | +------+----------------------+-----------+------------------------+ | 1 | 2017-06-28 15:09:46 | Huhu | 20170512200326_320.jpg | | 1 | 2017-06-28 15:09:46 | Huhu | 20170512200326_384.jpg | | 2 | 2017-04-28 15:16:18 | Miau | NULL | | 3 | 2017-04-28 15:17:14 | Kleff | NULL | +------+----------------------+-----------+------------------------+
He makes a cross-product out of the available Pictures, which is also logical. But I want him to just use the first Picture he finds. In the end it should look like that:
+------+----------------------+-----------+------------------------+ | ID | Date | TitleGer | Filename | +------+----------------------+-----------+------------------------+ | 1 | 2017-06-28 15:09:46 | Huhu | 20170512200326_320.jpg | | 2 | 2017-04-28 15:16:18 | Miau | NULL | | 3 | 2017-04-28 15:17:14 | Kleff | NULL | +------+----------------------+-----------+------------------------+
Tried several hours but couldn’t get it to work. Please help!
Answer
The easiest approach may be to select one IDPicture per IDBlog from PicturesJoin only:
SELECT b.ID, b.Date, b.TitleGer, p.Filename FROM Blogs b LEFT JOIN ( SELECT IDBlog, MIN(IDPicture) AS IDPicture FROM PicturesJoin GROUP BY IDBlog ) pj ON pj.IDBlog = b.ID LEFT JOIN Pictures p ON p.ID = pj.IDPicture ORDER BY b.Date DESC;