Help Me to Build MySQL Query

Discussion in 'MySQL' started by sandipitcslive, Mar 25, 2011.

0
  1. #1
    I've tables:
    1.star_article(article_id``itemid``title`): Stores Submitted Articles
    2.star_item(`itemid``cat_id``title`): Stores different items under several cat_id

    Brief:Under one Category(cat_id = "10") there are 3 Items(2,21,23)
    Under Every itemid there are several articles are submitted
    e.g:Under itemid=2 article_id=24, article_id=25, article_id=29 are submitted.

    I've retrieved latest submitted articles of every itemids under 1 cat_id: With this query:
    SELECT waa.article_id,waa.title,star_item.itemid FROM star_article as waa LEFT JOIN star_item ON waa.itemid=star_item.itemid WHERE waa.article_id IN(SELECT MAX(article_id) FROM star_article GROUP BY itemid) AND star_item.itemid IN(Select itemid from star_item WHERE cat_id=10)order by waa.article_id desc

    Now, I want to get top 5 articles of every itemid like this:

    article_id title itemid
    Latest article Article Title 21 ('coz 21 had submitted last)
    Latest article Article Title 23 ('coz 23 had submitted b4 21)
    Latest article Article Title 2 ('coz 2 had submitted b4 23)
    2nd Latest article -do- 21 ('coz 21 had submitted last)
    2nd Latest article -do- 23 ('coz 23 had submitted b4 21)
    2nd Latest article -do- 2 ('coz 2 had submitted b4 23)
    and so on. . .

    Please Help me write this complex query. . .
     
    Last edited: Mar 25, 2011
    sandipitcslive, Mar 25, 2011 IP
  2. sandipitcslive

    sandipitcslive Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Isn't there any one to help me write this kind of query???
     
    sandipitcslive, Mar 25, 2011 IP
  3. sandipitcslive

    sandipitcslive Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Help me Please!... If any questiion about this post... please ask... I'm here!...
     
    sandipitcslive, Mar 27, 2011 IP
  4. Sky AK47

    Sky AK47 Member

    Messages:
    298
    Likes Received:
    8
    Best Answers:
    1
    Trophy Points:
    45
    #4
    Describing of what you exactly want would help enormous as this post is a MESS.
    As I understand:
    You have a table with items which hold categories and titles. (I assume we can neglect the category table as you're giving the example of cat_id 10)
    You have a table with articles which fall under items and holds titles.
    You'd like to get the 5 last submissions of each item_id?
    The query you gave us seems to be correct next to the subquery which groups everything. removing that should fix it!
    SELECT star_article.article_id, star_article.title, star_item.itemid FROM star_article LEFT JOIN star_item ON star_article.itemid = star_item.itemid WHERE star_item.itemid IN (SELECT itemid FROM star_item WHERE cat_id = 10) ORDER BY star_article.article_id DESC
    Code (markup):
     
    Sky AK47, Mar 29, 2011 IP