SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with query from 2 tables

    Hi!

    I have a slight problem with mySQL query. I've got news page and there are categories where my news are. I want to select all categories and 4 latest news into the each category.

    Tables are:

    1) news_cat[nid(int) auto_increment, cat_name(varchar)]
    2) news[uid(int) auto_increment, nid(int), news_title(varchar), news_text(text), date_added(datetime)]

    And let's say I want to pull all categories from news_cat and then 4 latest news into the each category. I can't figure out this query...Too n00b for this I think

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select nc.cat_name
         , n1.news_title
         , n1.news_text
         , n1.date_added
      from news_cat as nc
    inner
      join news as n1
        on nc.nid = n1.nid
    inner
      join news as n2
        on n1.nid = n2.nid
       and n1.date_added <= n2.date_added
    group
        by nc.cat_name
         , n1.news_title
         , n1.news_text
         , n1.date_added
    having count(*) <= 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok this works, but one problem more...

    I got the result like that now:

    Category 1
    NewsTitle1

    Category 1
    NewsTitle2

    Category 2
    NewsTitle3

    If there are more than one news in category, then it puts two same category names into the page

  4. #4
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe is the simplest way quering all categories separately like:

    Code:
    SELECT nc.cat_name, n.news_title, n.news_text, n.date_added FROM news AS n, news_cat AS nc WHERE nc.nid=n.nid HAVING COUNT(*) <=4 ORDER BY n.date_added ASC


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •