Menu
Community /Pin to ProfileBookmark
@NogDogJul 27.2021 — #Maybe...
Change the
Untested, but I'm about 85% confident. :)@NogDogJul 28.2021 — #@kiwis80#1634804
My first approach failed, so I tried this. See if you think it solves what you're looking for...
http://sqlfiddle.com/#!9/c648ec/1
For posterity's sake, the SQL I used there:
Feels ugly, but seems to work.
JOIN SELECT Single Row
I have a table. Which has an index ID on it and a bunch of other data. I then have a second table with it’s own unique ID but also the parentID from table 1. In addition to this on table 2 I have a varchar and date column.
What I want to do it select all records from table 1. (easy) Then 1 row from table 2 for each corresponding parentID. The row I want is the the row with the latest date so sorting by date column and limiting to 1 record I assume.
How can I join the two tables, order and limit the second table only?
Sign in
to post a comment3 Comments(s) ↴
0
@NogDogJul 27.2021 — #Maybe...[code=sql]
select t1.*, t2.*
from table_1 as t1
inner join (
select * from table_2
where parentID = t1.ID
order by date_col desc limit 1
) as t2
[/code]Change the
inner joinleft joinUntested, but I'm about 85% confident. :)
0
@NogDogJul 28.2021 — #My first approach failed, so I tried this. See if you think it solves what you're looking for...
For posterity's sake, the SQL I used there:
[code=sql]
select m.*, c.*
from main m
inner join child c on c.parent_id = m.id
where c.id = (
select id from child
where parent_id = m.id
order by date desc limit 1
)
[/code]Feels ugly, but seems to work.