12 Replies - 636 Views - Last Post: 28 April 2018 - 11:20 AM Rate Topic: -----

#1 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Joining two tables, group by, and order by date

Posted 28 April 2018 - 08:56 AM

I have 2 tables: posts that a user post and contain some data such as: Post title, post creator ID, and date posted.
and posts_messages which are basically "comments" that anyone can write on that specific post.
What I want to do is: Get only the row of the last message posted on each different post, and order it by the last message posted on any post, with the data about that post.
The tables (with data examples) look like:

posts:

-----------------------------------------------------------------------------
|   post_id  | post_creator_id  |      post_title     |       date          |
-----------------------------------------------------------------------------
|    1       |        100       |   Hello All         | 2018-04-20 11:01:51 |
-----------------------------------------------------------------------------
|    2       |        14        |   Good morning      | 2018-04-21 17:11:46 |
-----------------------------------------------------------------------------
|    3       |        213       |   Lovely Day        | 2018-04-22 21:20:18 |
-----------------------------------------------------------------------------
|    4       |        55        |   Nice Title!       | 2018-04-23 23:30:11 |
-----------------------------------------------------------------------------




posts_messages:
---------------------------------------------------------------------------
| message_id | post_id  | poster_id | message_text |       date           |
---------------------------------------------------------------------------
|    8       |    1     |   98      | Hello world  |  2018-04-27 12:02:22 |
---------------------------------------------------------------------------
|    9       |    4     |   123     |    Hi all    |  2018-04-27 13:11:11 |
---------------------------------------------------------------------------
|    10      |    4     |   77      | Looking good |  2018-04-27 13:20:17 |
---------------------------------------------------------------------------
|    11      |    1     |   101     | Great idea   | 2018-04-27 14:45:15  |
---------------------------------------------------------------------------



In the above example, the data I need is: rows 10 and 11 from posts_messages - because these are the last messages posted on posts 1 and 4 (2 different posts). And for each post: 1 and 4, I need to get all the data (post_id, post_creator_id, post_title, date).
and order by the posts_messages date, so row 11 is first and 10 is second
I did try some joins but I am unable to get the content of posts that I need:

"SELECT posts.* 
FROM posts
INNER JOIN (SELECT post_id max(post_id) mid 
FROM posts_messages
GROUP BY post_id) mes ON mes.post_id = posts.post_id
ORDER BY mes.mid DESC";



It does order it but I don't get the content of the messages (message_text). using PHP I get "undefined index" for "message_text" for example. This is how I try to access it using PHP:


[code]
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['post_id']; //ok
echo $row['message_text']; //undefined index
}

This post is quite long and confusing, if there's something you think is written badly tell me so I can fix it,
Thanks!

This post has been edited by Foobarer: 28 April 2018 - 09:18 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Joining two tables, group by, and order by date

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14924
  • View blog
  • Posts: 59,580
  • Joined: 12-June 08

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 09:26 AM

The key should be a mix of the aggregate function 'max', group by, and a subquery.

select  max(date_entered) as date_entered
from #posts_messages

Yields
date_entered
-----------------------
2018-04-27 14:45:15.000


Ok.. so that's the max (aka the most recent datetime) of the whole table.. nice, but not effective.


If you group by the 'post id', AND do MAX, then you should get the maximum date for each post_id
select post_id
, max(date_entered) as date_entered
from #posts_messages
group by (post_id)


post_id     date_entered
----------- -----------------------
1           2018-04-27 14:45:15.000
4           2018-04-27 13:20:17.000



Now that's cooking with fire!

To get the rest of the row you can take that the query above, use it as a sub-query, and join against the table again.

select a.*
from #posts_messages a
join (
	select post_id, max(date_entered) as date_entered
	from #posts_messages
	group by (post_id)
	) b on a.post_id = b.post_id and a.date_entered = b.date_entered

message_id  post_id     poster_id   message_text                                       date_entered
----------- ----------- ----------- -------------------------------------------------- -----------------------
10          4           77          Looking good                                       2018-04-27 13:20:17.000
11          1           101         Great idea                                         2018-04-27 14:45:15.000


Now that's cooking with propane and propane accessories.

SQL can get pretty hairy with the joins, but - like legos - if you start small and add in a few blocks in the right area you can general head to the right direction.
Was This Post Helpful? 1
  • +
  • -

#3 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 09:37 AM

That's a really good recipe, but a few ingredients are missing:
Because now the message_text is defined, but the other data is not present:

Quote

And for each post: 1 and 4, I need to get all the data (post_id, post_creator_id, post_title, date)

***Edit: In fact, the date from posts is actually not necessary, so what is missing is the post_title and post_creator_id
So I also need the entire row from posts as well (and not just posts_messages)

But thanks, your posts also helps understanding what my other mistakes were: :rockon:

This post has been edited by Foobarer: 28 April 2018 - 09:39 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14924
  • View blog
  • Posts: 59,580
  • Joined: 12-June 08

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 09:43 AM

Quote

Because now the message_text is defined, but the other data is not present:

What other data? That last example gives you the entire row from that table.

Quote

so what is missing is the post_title and post_creator_id

Yes.. so you take that last query, and make that a subquery to join off the table 'posts'.. then Bob's your uncle.
Was This Post Helpful? 1
  • +
  • -

#5 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 09:58 AM

I dont exactly know the syntax,It was also a problem in the original post - just now we flipped the data: at first I had the post_title and didn't have message_text and now I have message_text but not post_title. I don't know where exactly and what syntax to use in order to add these 2 columns (post_title and post_creator_id)

This post has been edited by Foobarer: 28 April 2018 - 09:58 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14924
  • View blog
  • Posts: 59,580
  • Joined: 12-June 08

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 10:07 AM

Last query.. lines 3-7.. notice how I joined off a query and not a table? Same thing except use the whole query there and join off your other table.
Was This Post Helpful? 1
  • +
  • -

#7 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 10:15 AM

Something like that? join after join? (never used a few together, so forgive me for the ignorance :))

select a.*
from #posts_messages a
join (
	select post_id, max(date_entered) as date_entered
	from #posts_messages
	group by (post_id)
	) b on a.post_id = b.post_id and a.date_entered = b.date_entered
join (
	select post_title, post_creator_id 
	from #posts
	group by (post_id)
	) b on a.post_id = b.post_id

This post has been edited by Foobarer: 28 April 2018 - 10:16 AM

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14924
  • View blog
  • Posts: 59,580
  • Joined: 12-June 08

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 10:19 AM

No.

select
from table1 a
join (   <--- starting subquery with (
  select 
  from table2
  .. what ever other junk
) b <--- ending subquery with ).. giving it an alias of b
on a.col = b column <--- per normal joins tell it which columns are off of.



Super similar to

select table1 a
join table2 b 
on a.col = b.col


Instead of the 'join table2' you have a query inside ( and ).. and are joining off that.
Was This Post Helpful? 1
  • +
  • -

#9 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 10:27 AM

But I don't know where to place that second subquery, I know it should be a subquery that looks something like
SELECT post_title, post_creator_id FROM posts //and more stuff

I know in the last 2 posts you tried to make me write the correct one, but I can't figure out :)
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14924
  • View blog
  • Posts: 59,580
  • Joined: 12-June 08

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 10:33 AM

Previous post; first query. That's the format.
Was This Post Helpful? 1
  • +
  • -

#11 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 10:55 AM

But how is that the format? In the format it says:
select
from table1 a
join (
  select 
  from table2 // <-- but in the original post, you use table 1 here as well
) b 
on a.col = b column



In the original post you use table1 where you put table2:

select a.*
from #posts_messages a //<--table1
join (
	select post_id, max(date_entered) as date_entered
	from #posts_messages//<--also table1 (same table!)
	group by (post_id)
	) b on a.post_id = b.post_id and a.date_entered = b.date_entered


That's why I'm confused

This post has been edited by Foobarer: 28 April 2018 - 10:55 AM

Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14924
  • View blog
  • Posts: 59,580
  • Joined: 12-June 08

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 10:59 AM

Absolutely not. There is a 'table1' and a 'table2'.
Was This Post Helpful? 1
  • +
  • -

#13 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Joining two tables, group by, and order by date

Posted 28 April 2018 - 11:20 AM

Ok so for what I need there are actually total of 3 tables (the new table, posts_messages, and posts) and the one I need to add sub query to is posts. maybe this is that syntax:
select a.*
from #posts_messages a
join (
	select post_id, max(date_entered) as date_entered // i need this part from posts_messages so I can't remove this
	from posts_messages
	group by (post_id)
        AND //I'm sure thats not how to add the second sub query, but I'm out of ideas
        select post_title, post_creator_id 
	from posts
	) b on a.post_id = b.post_id and a.date_entered = b.date_entered


This post has been edited by Foobarer: 28 April 2018 - 11:22 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1