I have 3 tables, categories / posts / gallery.
Needs to return a list of categories sorted in order of csort with the first gsort photo from the newest post. If no photo or post then show null.
The first photo for each post will generally be gsort=1, however if this photo is removed, it needs to show the lowest gsort value as i will then show a placeholder image.
categories
----------
cid cname curl csort
1 ccc ccc/ 3
2 bbb bbb/ 2
3 aaa aaa/ 1
4 ddd ddd/ 4
posts
-----
pid pname purl cid padded poffon
1 apples apples.html 2 2019-01-02 10:11:12 1
2 orange orange.html 1 2019-01-02 10:12:00 1
3 grape red-grapes.html 1 2019-01-06 10:15:12 1
4 banana bannas.html 2 2019-01-08 10:19:54 0 //Note: this post is hidden
5 kiwi kiwi-fruit.html 3 2019-01-10 10:26:20 1
gallery_photos
--------------
gid pid gsrc gsort
1 1 img01a.jpg 6
2 1 img01b.jpg 2
3 1 img01c.jpg 4
4 3 img03a.jpg 2
5 3 img03b.jpg 1
6 3 img03c.jpg 4
7 3 img03d.jpg 3
8 2 img02a.jpg 1
9 1 img01d.jpg 5
10 1 img01e.jpg 3
Expected results>>>
cid cname curl gsrc
3 aaa aaa/ null //Note: no photos exist for this post
2 bbb bbb/ img02a.jpg //Note: 1 post exists for this category, show gsrc for lowest gsort
1 ccc ccc/ img03b.jpg //Note: 2 posts exist for this category, show gsrc for lowest gsort for the latest post date
4 ddd ddd/ null //Note: no posts exist for this category... a holding page with other content will be shown
Here's what I've got so far for the query BUT its returning too many results:
SELECT C.id
, C.name
, GA.medium_path AS gsrc
, GA.gsort
FROM CATEGORIES C
LEFT
JOIN POSTS P
ON C.cid = P.cid
JOIN
( SELECT cid
, MAX(padded) max_date
FROM POSTS
GROUP
BY cid
ORDER
BY padded DESC
) t
ON t.max_date = P.padded
AND C.cid = P.cid
LEFT
JOIN GALLERY GA
ON GA.pid = P.pid
Many thanks in advance
Here is a solution that extends your logic. It uses a series of LEFT JOINs to identify, then pull out the latest post, and then identify and pull out the earlier picture.
SELECT
c.cid,
c.cname,
c.curl,
g.gsrc,
g.gsort
FROM
categories c
LEFT JOIN (SELECT cid, MAX(padded) padded FROM posts WHERE poffon = 1 GROUP BY cid) pmax ON pmax.cid = c.cid
LEFT JOIN posts p ON p.cid = c.cid AND p.padded = pmax.padded
LEFT JOIN (SELECT pid, MIN(gsort) gsort FROM gallery_photos GROUP BY pid) gmin ON gmin.pid = p.pid
LEFT JOIN gallery_photos g ON g.pid = p.pid AND g.gsort = gmin.gsort
ORDER BY c.cname
I tested it in this db fiddle and the result matches your expected output (excepted the gsrc for cid 2, which I believe should be 'img01b.jpg' instead of 'img02a.jpg').
WITH
categories AS (
SELECT 1 cid, 'ccc' cname, 'ccc/' curl, 3 csort
UNION SELECT 2, 'bbb', 'bbb/', 2
UNION SELECT 3, 'aaa', 'aaa/', 1
UNION SELECT 4, 'ddd', 'ddd/', 4
),
posts AS (
SELECT 1 pid, 'apples' pname, 'apples.html' purl, 2 cid, '2019-01-02 10:11:12' padded, 1 poffon
UNION SELECT 2, 'orange', 'orange.html', 1, '2019-01-02 10:12:00', 1
UNION SELECT 3, 'grape', 'red-grapes.html', 1, '2019-01-06 10:15:12', 1
UNION SELECT 4, 'banana', 'bannas.html', 2, '2019-01-08 10:19:54', 0
UNION SELECT 5, 'kiwi', 'kiwi-fruit.html', 3, '2019-01-10 10:26:20', 1
),
gallery_photos AS (
SELECT 1 gid, 1 pid, 'img01a.jpg' gsrc, 6 gsort
UNION SELECT 2, 1, 'img01b.jpg', 2
UNION SELECT 3, 1, 'img01c.jpg', 4
UNION SELECT 4, 3, 'img03a.jpg', 2
UNION SELECT 5, 3, 'img03b.jpg', 1
UNION SELECT 6, 3, 'img03c.jpg', 4
UNION SELECT 7, 3, 'img03d.jpg', 3
UNION SELECT 8, 2, 'img02a.jpg', 1
UNION SELECT 9, 1, 'img01d.jpg', 5
UNION SELECT 10, 1, 'img01e.jpg', 3
)
SELECT
c.cid,
c.cname,
c.curl,
g.gsrc,
g.gsort
FROM
categories c
LEFT JOIN (SELECT cid, MAX(padded) padded FROM posts WHERE poffon = 1 GROUP BY cid) pmax ON pmax.cid = c.cid
LEFT JOIN posts p ON p.cid = c.cid AND p.padded = pmax.padded
LEFT JOIN (SELECT pid, MIN(gsort) gsort FROM gallery_photos GROUP BY pid) gmin ON gmin.pid = p.pid
LEFT JOIN gallery_photos g ON g.pid = p.pid AND g.gsort = gmin.gsort
ORDER BY c.cname;
cid | cname | curl | gsrc | gsort --: | :---- | :--- | :--------- | ----: 3 | aaa | aaa/ | null | null 2 | bbb | bbb/ | img01b.jpg | 2 1 | ccc | ccc/ | img03b.jpg | 1 4 | ddd | ddd/ | null | null