Query join get, add most recently created field to row

Question

I'm having a tough time with a query.

I have a table called project_slugs that contains the following fields id, project_id, slug, created.

I have a master table projects that contains various fields.

There is a foreign key relation via projects.id and project_slugs.project_id.

project_slugs can contain multiple slugs for any given project.

I want to get a single result that has all the fields from the projects table plus the most recently created projects_slug.slug for that given project. This should be accomplished by WHERE projects_slug.slug = 'some-slug' where 'some-slug' may or may not be the most recent slug.

I am able to join the tables successfully, however I am unsure how to incorporate the bolded logic above.

Here is my current query:

SELECT projects.*, project_slugs.slug 
FROM `project_slugs` LEFT JOIN 
     `projects`
     ON project_slugs.project_id = projects.id
WHERE project_slugs.slug = 'some-slug'

Slugs:

Projects:

Expected Output with input:

SELECT projects.*, project_slugs.slug
FROM `project_slugs` LEFT JOIN 
     `projects`
     ON project_slugs.project_id = projects.id
WHERE project_slugs.slug = 'star-management-week-2015'

Expected output would be what you see in the last image + the most recent slug value that is circled as ascertained by project_slugs.created. Given any project_slugs.slug (new or old) as the identifier. I'm not even sure if this is possible in sql. The logic is rather simple in php.

Since there's no timestamp field nor date_created, I'll assumed that the most recent created slug is the last inserted ID, assuming that, 'ORDER BY slug.id DESC LIMIT 1' should do it. — Nick, May 05 '19 at 06:15
I am confused you say you want the most recently created slug and immediately after that ' This should be accomplished by WHERE' these 2 statements contradict each other. Can you expand on what you mean please. Adding sample data and expected output (as text) would help. — P.Salmon, May 05 '19 at 06:29
Let's say in the slug table there are 3 entries for the same project bob-1, bob-2, and bob-3 (each time user changes project title it gets a new slug for seo purposes, but slugs are kept in table to prevent 404 errors from search engine indexes). I query the table with say, slug = bob-2. As the slug only exists as a fk relationship with the projects table I need to use the where statement to select the project by way of the slug table. However, I no longer want bob-2 in my final output, as it is not the most recent slug, bob-3 is. Does that make more sense? — Alex, May 05 '19 at 06:35
Great, marked as duplicate without even reading the question. **The answer in the question linked involves 1 table.** mine clearly involves **two** — Alex, May 05 '19 at 06:46
I've provided another duplicate which does use two tables. But if you could provide some sample data from the `projects` and `project_slug` tables and expected output for that data I might be persuaded to reopen as this does appear to have some differences due to the search criteria. — Nick, May 05 '19 at 07:04
Where did title come from - there is no title in the project_slugs table? And how do you know that bob-1,bob-2 and bob-3 are all title changes for a project? And does projects contain the most recent slug title or the original slug title? — P.Salmon, May 05 '19 at 07:12
@P.Salmon the title is generated whenever the user updates/inserts a project. in both cases it is generated from the name/title of the project and is made-url-friendly and put in to the slugs table (unless on update the title doesn't change). the relationship is the between `project_id` (slugs table) and the `id` (projects table). i know it is related to the project because of this. so if i say `select slug from project_slugs where project_id = someid orderby desc` i will get all the slugs for that given project in newest to oldest order. `projects` as such contains no slugs. — Alex, May 05 '19 at 07:16
@Nick i've added some pictures. let me know if more is required. — Alex, May 05 '19 at 07:19
@Alex I've reopened. Since I did I'm not going to answer but I think there's enough info in the question now for you to get a good answer. — Nick, May 05 '19 at 07:53
Appreciate it guys ;) It's a bit confusing I know but hopefully it makes sense now. — Alex, May 05 '19 at 07:56
I suspect what you need to do is find the project id based on a slug value passed, then find the most recent created date for the project id and then join to project. Have a look at http://sqlfiddle.com/#!9/a08897/1 this. I have commented out the where clause because I was interested in all projects you may wish to put it back in.And you may or may not want a left join. — P.Salmon, May 05 '19 at 07:59
@P.Salmon you should have added it as an answer! then i would have been able to give you an upvote. your solution works as well, although I prefer the accepted answer as it is easier for me to understand and imo less verbose. — Alex, May 05 '19 at 20:10

ScaisEdge · Answer 1 · 2019-05-05T18:50:03.167

1

You should use a join also with the subquery for max created group by project_id

SELECT projects.*, 
project_slugs.slug 
FROM projects 
inner join  (
  select project_id, max(created) max_created 
  from  project_slugs 
  group by  project_id 
) t on t.project_id = projects.id 
inner join  `project_slugs` ON project_slugs.project_id = projects.id 
AND project_slugs.created = t.max_created

edited May 05 '19 at 18:50

answered May 05 '19 at 08:21

ScaisEdge

131,976
10
91
107

answer updated .. missing join word .. after inner – ScaisEdge May 05 '19 at 18:41
gives me empty result set even though the slug `star-management-week-2015` exists: https://imgur.com/a/z3AqJtx – Alex May 05 '19 at 18:45
You slug is not referred to the max date ..(most recent slug for project) you question seems not coherent ...try avoid the match for slug an check the result for all the project .. – ScaisEdge May 05 '19 at 18:49
it's ok, gordon's answer works. thanks for the help! – Alex May 05 '19 at 20:03

score 1 · Accepted Answer · edited May 05 '19 at 18:42

1

I think you want the most recent slug on any project that has the slug in question. If so:

select p.*,
       (select ps.slug
        from project_slugs ps
        where ps.project_id = p.id
        order by ps.created desc
        limit 1
       ) as most_recent_slug
from projects p
where exists (select 1
              from project_slugs ps
              where ps.project_id = p.id and
                    ps.slug = 'some-slug'
             );

If you actually want only the projects where the most recent slug is the specified slug, then in MySQL, you can use the extended having clause. Replace the where with:

having most_recent_slug = 'some-slug'

edited May 05 '19 at 18:42

Alex

9,215
8
39
82

answered May 05 '19 at 12:06

Gordon Linoff

1,242,037
58
646
786

There is an issue with the above code: `#1054 - Unknown column 'p.project_id' in 'where clause'` – Alex May 05 '19 at 18:36
Edit: i fixed it the issue in your answer and it works; issue with`p.project_id` should be `p.id`. – Alex May 05 '19 at 18:43
1

I also love how I can simply remove the `where exists` and use it to get all rows with their respective most recent slugs. thank you so much! – Alex May 05 '19 at 20:08

Query join get, add most recently created field to row

2 Answers2