#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0

    A little help with a left join


    Hi folks,

    I'm working on a very simple forum site which uses PHP and MySQL. I have two tables: Post and Thread. This is how the two tables are defined:

    create table post(
    author varchar(16) not null,
    clocked datetime not null,
    content varchar(4096) not null,
    subject varchar(64),
    primary key(author, clocked),
    foreign key(author) references user(alias)
    );

    create table thread(
    authororg varchar(16) not null,
    clockedorg datetime not null,
    author varchar(16) not null,
    clocked datetime not null,
    primary key(author, clocked),
    foreign key(author, clocked) references post(author, clocked),
    foreign key(authororg, clockedorg) references post(author, clocked)
    );

    After countless hours of trial & error (yes I've have very little experience with select queries) I managed to find the proper select statements for 3 out of 4 needed queries:

    1) Get threads (check)
    2) Get replies to a specific post (check)
    3) Get posts with no replies (to-do!)

    Please (and I'm almost begging here), if any of you could give me a hint on #3 query I'd be really grateful


    Cheers from Scandinavia,
    Rasmus


    Oh, in case a rookie like myself is reading this, here is what I ended up with regarding query 1-3:

    #1 - get threads:
    SELECT post.author, post.clocked, post.subject FROM post
    LEFT JOIN thread
    ON post.clocked = thread.clocked
    AND post.author = thread.author
    WHERE thread.clockedorg IS NULL
    ORDER BY post.subject, post.clocked ASC

    #2 - get x-post replies:
    SELECT post.author, post.clocked, post.subject FROM post
    JOIN thread
    ON post.clocked = thread.clocked
    AND post.author = thread.author
    where thread.authororg = "nml"
    and thread.clockedorg = "2013-05-22 20:00:00"
    ORDER BY post.subject, post.clocked ASC
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    If table(thread) will not have an entry if there are no replies then this should do it:
    Code:
    select post.author, post.clocked, post.subject
     from  post
    where  not exists ( select *
                         from  thread
                        where  thread.author  = post.author
                         and   thread.clocked = post.clocked )
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by spacebar208
    If table(thread) will not have an entry if there are no replies then this should do it:
    Code:
    select post.author, post.clocked, post.subject
     from  post
    where  not exists ( select *
                         from  thread
                        where  thread.author  = post.author
                         and   thread.clocked = post.clocked )
    Hi spacebar208, thanks for your reply. I really appreciate it. But unfortunately your query does the opposite: selects all posts with a reply.

    I can assure you that that a thread is nothing but a reference of posts, meaning that posts with no reply has no entry in the thread table.

    I've dumped MySQL dml to pastebin in case you need to verify the structure and data:
    http://pastebin.com/ke527Vvc

    The blog database is populated with 3 users, 2 unreplied posts (no thread entry), and a thread of 3 posts. Can I persuade you to give it another bash, please?

    Cheers,
    Rasmus
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by CodeBewildered
    I've dumped MySQL dml to pastebin in case you need to verify the structure and data:
    http://pastebin.com/ke527Vvc
    thanks, that helps, although the user data did not fit the user table definition

    your query #1 just returns all posts, so it doesn't really do the job of returning threads

    i think this statement reflects something that is in my opinion very unusual --
    I can assure you that that a thread is nothing but a reference of posts, meaning that posts with no reply has no entry in the thread table.
    that's not how most forums work

    each post either starts a new thread, or is in reply to an existing post in an existing thread

    thus, there should never be a thread without at least one post

    the fact that your database is organized differently is troubling
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    This should work for you:
    Code:
    select author, clocked, content, subject from post;
    
    author clocked             content subject
    ad     2013-05-22-11.00.00 Content Unreplied post 2
    ad     2013-05-22-21.00.00 Content Re: Post with reply
    nml    2013-05-22-20.00.00 Content Post with reply
    nml    2013-05-22-22.00.00 Content Re:Re: Post with reply
    rasmus 2013-05-22-10.00.00 Content Unreplied post 1
    
    
    select authororg, clockedorg, author, clocked from thread;
    
    authororg clockedorg          author clocked
    nml       2013-05-22-20.00.00 ad     2013-05-22-21.00.00
    nml       2013-05-22-20.00.00 nml    2013-05-22-22.00.00
    
    
    select post.author, post.clocked, post.subject
     from  post
    where  not exists ( select *
                         from  thread
                        where  thread.authororg  = post.author
                         and   thread.clockedorg = post.clocked );
    
    -- These 'post' rows do not have a matching row in 'thread' even though your subject has "with reply" in it
    author clocked             subject
    ad     2013-05-22-11.00.00 Unreplied post 2
    ad     2013-05-22-21.00.00 Re: Post with reply
    nml    2013-05-22-22.00.00 Re:Re: Post with reply
    rasmus 2013-05-22-10.00.00 Unreplied post 1
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    thanks, that helps, although the user data did not fit the user table definition

    your query #1 just returns all posts, so it doesn't really do the job of returning threads
    My apologies for that. I've edited the post (the get threads, and get posts with replies was in fact the same query, so I've updated that as well).

    i think this statement reflects something that is in my opinion very unusual --that's not how most forums work

    each post either starts a new thread, or is in reply to an existing post in an existing thread
    I believe you when you say this is not how most forums work :-) But my "design" is a simple 1-N entity relationship between post and thread which will work as well. Actually I'm not sure I understand why you would register a post in a thread if its the only post in that thread (in my head that's just a post not part of a thread). But what do I know - I'm just a rookie.

    Given my (albeit unorthodox) design, are you able to help me out with the query? I'm sure its very simple, but for some reason I can't seem to figure it out.

    Cheers,
    Rasmus
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0
    -- These 'post' rows do not have a matching row in 'thread' even though your subject has "with reply" in it
    This part of the dml is what creates the thread (registers the two replies in the thread table with foreign keys to the "thread starter" post):

    #Bind post and replies together
    insert into thread
    values('nml', '2013-05-22 20:00:00','ad', '2013-05-22 21:00:00', default);
    insert into thread
    values('nml', '2013-05-22 20:00:00','nml', '2013-05-22 22:00:00', default);
    The thread becomes this:
    1. Post wth reply
    2. Re: Post wth reply (foreign keys to #1)
    3. Re:Re: Post with reply (foreign keys to #1)

    The two post rows with subjects "Unreplied post 1" and "Unreplied post 2" are not registered in thread, hence they are posts without replies (and therefor not in a thread):
    1. Unreplied post 1
    2. Unreplied post 2

    It seems my design confuses you a little bit. But as I wrote before it's just a 1-N relationship. And since I'm able to select threads, it should be possible to "negate" that select (I hope so at least)

    Cheers,
    Rasmus

IMN logo majestic logo threadwatch logo seochat tools logo