CODEWITHSUNDEEP
sqlpostgresqlsubquery
Ty Kroll
Ty Kroll

Reputation: 1395

SQL: return comments from other users that occur after my comment on a post I didn't create

Sorry for the ridiculous question title. I didn't know how to generalize it.

I need a list of comments that occur after my comments left on a post I did not create.

I'm using postgres. My post table structure:

CREATE TABLE posts (
    id integer NOT NULL,
    parent_id integer DEFAULT (-1),
    msg character varying(140),
    user_id integer,
    create_dte numeric(10,0) DEFAULT 0
);

There can only be one level of comments. Original posts have a parent_id of -1. Comments have a parent_id of an original post's id.

I can get my comments on posts I didn't create:

select p1.id, p1.msg 
from   posts p1 
where  p1.user_id = MY_USER_ID 
and    p1.parent_id in (
    select p2.id 
    from   posts p2 
    where  p2.parent_id = -1 
    and    p2.user_id != MY_USER_ID)

Can someone give me a hint on how to select the posts that have the same parent_id and a greater create_dte?

Upvotes: 0

Views: 58

Answers (2)

Ty Kroll
Ty Kroll

Reputation: 1395

I used the answer from @podiluska above as a jumping-off point for this solution:

select
    posts.*
from
    posts
        inner join
        (
            (
                select h1.parent_id, min(h1.create_dte_timestamp) as min_date
                from posts h1
                where
                    h1.parent_id != -1 and
                    h1.user_id = USER_ID and
                    h1.parent_id not in
                    (
                        select h2.id
                        from posts h2
                        where h2.id = h1.parent_id and h2.user_id = USER_ID
                    )
                    group by h1.parent_id
            )
        ) lastpost
        on posts.parent_id = lastpost.parent_id

where
    posts.create_dte_timestamp > lastpost.min_date and
    posts.user_id != USER_ID

Upvotes: 0

podiluska
podiluska

Reputation: 51504

select 
    posts.*
from 
    posts
        inner join
    (
        select parent_id, MAX(create_dte) lastpostdate
        from posts 
        where user_id=2
        group by parent_id
    ) lastpost
        on posts.parent_id = lastpost.parent_id
        and posts.create_dte>lastpost.lastpostdate

Upvotes: 1

Related Questions