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

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0

    INNER JOIN with ORDER BY


    Hello guys.
    I have a question that you experts will know quickly.
    I have two tables, one is newsDB and one is commentNewsDB.

    newsDB has news in it, commentNewsDB has comments of those news. This is the structure of those two tables.
    newsDB :
    ID (primary key)
    header
    content
    author
    date

    commentNewsDB:
    ID (primary key)
    IDofNews (foreign key so i know which comment belongs to which news)
    content

    And now the question is:
    I want to select the latest 3 news and count of how many comments this news has.
    I tried with this SQL query, but it doesnt work:

    SELECT newsDB.header, newsDB.content, newsDB.author, newsDB.date, COUNT(commentOfNews.IDofNews)
    FROM newsDB INNER JOIN commentOfNews ON newsDB.ID=commentNews.IDofNews ORDER BY date DESC LIMIT 3;

    Please, help, I dont' know how to fix this Thank you very much!
  2. #2
  3. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    whats the error? i would suggest grouping the results by the columns asked for and alias the count to 'commentCount' for reference:

    select a, b, c, count(d) as commentCount .. etc ..
    group by a, b, c, commentCount
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    Hello friend.
    Thank you for your help.
    I doesnt throw me an error, it just select the first news with ID = 1 and the WHOLE COUNT of comments in the table.

    I will try with your suggestion now.
    Thanks again.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    I have this now, this returns the right count of comment for the selected news, however is it not sorted by the 3 latest news.

    "SELECT newsDB.header, newsDB.content, newsDB.author, newsDB.date, COUNT(IDofNews) as commentCount
    FROM newsDBINNER JOIN commentNewsDB
    ON newsDB.ID=commentNews.IDofNews
    GROUP BY newsDB.header, newsDB.content, newsDB.author, newsDB.dateORDER BY newsDB.date DESC LIMIT 3";

    However, if I add commentCount in GROUP BY it gives me an error.
  8. #5
  9. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    Can you post the error?
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    Warning: mysql_fetch_array() expects parameter 1 to be resource...
    It just doesnt select anything..
    Without commentCount in GROUP BY it select right count of comments it just doesnt sort them by date, I dont know why.
  12. #7
  13. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    Probably because you havent referenced the alias properly - try specifying date as DBNews.date

    and I was under the impression that your count was not correct as you had stated. Its hard to do this when I dont have the same tables and data you are working with - i was in the middle of creating those tables. Let me know if i should continue.
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    Hello, my friend.
    Of course I would be happy, If you could help me with you creating these 2 tables. Thank you very much.

    I posted the query that returns an error and I am aliasing as you said.

    Comments on this post

    • Matt1776 agrees : Good but lets keep the discussion here. Let me know if there is a problem ordering after adding the aliasing
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    Originally Posted by AndiAndi
    Hello, my friend.
    Of course I would be happy, If you could help me with you creating these 2 tables. Thank you very much.

    I posted the query that returns an error and I am aliasing as you said.
    What do you mean with aliasing ? Is there a syntax error in my latest SQL query?
    If I do this, it selects the right amount of comments but doesnt select the newsi in order(latest 3).

    "SELECT newsDB.header, newsDB.content, newsDB.author, newsDB.date, COUNT(IDofNews) AS commentCount
    FROM newsDB INNER JOIN commentNewsDB
    ON newsDB.ID=commentNewsDB.IDofNews
    GROUP BY newsDB.header, newsDB.content, newsDB.author, newsDB.date ORDER BY newsDB.date DESC LIMIT 3";

    If I do this:

    "SELECT newsDB.header, newsDB.content, newsDB.author, newsDB.date, COUNT(IDofNews) AS commentCount
    FROM newsDB INNER JOIN commentNewsDB
    ON newsDB.ID=commentNewsDB.IDofNews
    GROUP BY newsDB.header, newsDB.content, newsDB.author, newsDB.date ORDER BY newsDB.date, commentCount DESC LIMIT 3";
    , it doesnt do anything, returns that sql query didnt return anything.
  18. #10
  19. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    From your original post:

    SELECT newsDB.header, newsDB.content, newsDB.author, newsDB.date, COUNT(commentOfNews.IDofNews)
    FROM newsDB INNER JOIN commentOfNews ON newsDB.ID=commentNews.IDofNews ORDER BY date DESC LIMIT 3
    "date" can be "newsDB.date" the field name seems to be unique from any field in commentOfNews but I cannot be sure how MySQL will act on this.
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    Originally Posted by Matt1776
    From your original post:



    "date" can be "newsDB.date" the field name seems to be unique from any field in commentOfNews but I cannot be sure how MySQL will act on this.
    As you can see in my previous post, I fixed that and its still the same
  22. #12
  23. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    Ok this is something iv'e had to remind myself also so thanks for the opportunity. When you group by it automatically sets the count of the row its grouping so you dont have to group by the count, the count will be set. You simply need to group by the column names in table A and include the count of table B in your select. Observe:

    Code:
    mysql> select a.header, a.body, a.date, count(b.news_id) as bcount from news as a left join news_comments as b on a.id = b.news_id group by a.header, a.body, a.date order by a.date desc;
    +----------+-------+---------------------+--------+
    | header   | body  | date                | bcount |
    +----------+-------+---------------------+--------+
    | subject7 | body7 | 2013-04-23 17:32:45 |      2 |
    | subject3 | body3 | 2013-04-23 17:32:45 |      2 |
    | subject8 | body8 | 2013-04-23 17:32:45 |      2 |
    | subject4 | body4 | 2013-04-23 17:32:45 |      4 |
    | subject9 | body9 | 2013-04-23 17:32:45 |      6 |
    | subject5 | body5 | 2013-04-23 17:32:45 |      3 |
    | subject1 | body1 | 2013-04-23 17:32:45 |      2 |
    | subject6 | body6 | 2013-04-23 17:32:45 |      2 |
    | subject2 | body2 | 2013-04-23 17:32:45 |      2 |
    +----------+-------+---------------------+--------+
    9 rows in set (0.00 sec)
    so your query needs to change to:

    SELECT newsDB.header, newsDB.content, newsDB.author, newsDB.date, COUNT(commentNewsDB.IDofNews) AS commentCount
    FROM newsDB INNER JOIN commentNewsDB
    ON newsDB.ID=commentNewsDB.IDofNews
    GROUP BY newsDB.header, newsDB.content, newsDB.author, newsDB.date ORDER BY newsDB.date DESC LIMIT 3
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    Hello again, friend.
    You posted in the second query INNER JOIN, but in your code you wrote LEFT JOIN.

    So, I used LEFT JOIN and it works perfectly now.
    Thank you very much.
    So we have to use LEFT JOIN, but why do we have to use LEFT JOIN? This is the explanation, but I dont quite understand it, so if you could explain for the other community, so they can understand it too

    The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
    By that definition, it should work with INNER JOIN, but it doesnt though

    The INNER JOIN keyword returns rows when there is at least one match in both tables.
  26. #14
  27. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    LEFT JOIN = LEFT OUTER JOIN
    INNER JOIN = JOIN

    Take a look at this link , it helps to clarify things visually.

    And I do not see any difference in my result set using LEFT JOIN or INNER JOIN. As the explanation and understanding of the two follow - then I don't see why they would differ in this case either. What makes you say that they differ? Why does 'INNER JOIN' not work for you? Can you show me the difference in your result sets? Perhaps there is something your not telling us about your schema?
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    14
    Rep Power
    0
    Hello again, friend.
    I am terribly sorry, I was wrong, it works with INNER JOIN and LEFT JOIN.
    I tested it and it returns the same stuff as it should.
    At the end, I appreciate your help, you saved me quite some time
    I hope I'll be able to ask you some question in future If I won't know how do solve them
    Good bye!

IMN logo majestic logo threadwatch logo seochat tools logo