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

    Join Date
    Jan 2013
    Posts
    13
    Rep Power
    0

    Question Select articles and join its comments underneath it


    I have a simple article and comments system with the following tables:

    Articles table:

    Code:
         id | writer  | text
         1  | Bob    | first article
         2  | Marley | second article
    Comments table:

    Code:
     
        id | article_id | comment
         1 |      1     |  i love this article
         2 |      1     |  good one
         3 |      2     |  waiting for more
    I want to select each article with its comments underneath it. I use the following query:

    Code:
     SELECT * FROM articles LEFT JOIN comments ON articles.id = comments.article_id
    The results I get:

    Code:
    articles.id   | articles.writer | articles.text  | comments.id | comments.article_id | comments.comment
         1             | Bob             | first article  |    1        |         1           | i love this article
         1             | Bob             | first article  |    2        |         1           | good one  
         2             | Marley          | second article |    3        |         2           | waiting for more
    What I want:

    Code:
    articles.id   | articles.writer | articles.text  | comments.id | comments.article_id | comments.comment
         1            | Bob             | first article  |    1        |         1           | i love this article
        NULL          | NULL            |    NULL        |    2        |         1           | good one  
         2            | Marley          | second article |    3        |         2           | waiting for more
    How do I select each article once and then the comments? Like in the first article, it has 2 comments, how to avoid the article text, writer, and id from being outputted 2 times for each comment?

    Thanks
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    do the formatting (in your case leaving out repeated values) in your application, not with SQL.

    I think this is common misunderstanding caused by spreadsheet programs like Excel. SQL tables and result sets are supposed to hold data, not be pretty. In your case, (mis-)using NULL in the sense of "see above" doesn't even make sense, because your set isn't ordered. So there is no "above".

    Use your JOIN to get the raw data. And then use your application to format the data in any way you want. By the way, rather than putting everything in a big table (with a lot of repetitions), I'd have the article data on top and a table of all comments below that. But that's up to you, of course.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    13
    Rep Power
    0
    Hey

    I sort results but didn't add it to the query as it's out of scope of the question. Regarding data output I display each article then the comments below it not in a table like that...this is only for illustration. I can easily display the article only once but it doesn't make sense retrieving data from sql and then not displaying it...also this redundant data (repeated values) add to the memory and slow down loading the page...thats why i'm searching for a query to get only the data i need without repeated values.

    Thanks
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    No offense, but I think you're really on the wrong track with that "optimization".

    Have you actually done benchmarks? How many comments will you even have? I mean, more than 1,000 on a single page would be pretty much unusable.

    Unless you fetch thousands of rows all into one big array, this is a complete waste of time, because the used memory doesn't even add up (PHP has a garbage collector to take care of that). And if you do collect all rows, that would be your problem.

    I understand that it might seem strange to you that the values are repeated. But this is how joins in the relational database model work. If you don't like it, you might have to look for a different model.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by butlimous
    ...thats why i'm searching for a query to get only the data i need without repeated values.
    here ya go... Minimize Bandwith in One-to-Many Joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    13
    Rep Power
    0
    Thanks r937 very much I think that is exactly what I need
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Select articles and join its comments underneath it


    How did u insert comments into comments table for particular article ?

IMN logo majestic logo threadwatch logo seochat tools logo