Results 1 to 8 of 8

Thread: prevent loop

  1. #1
    Join Date
    Nov 2008
    Posts
    17

    Unanswered: prevent loop

    Hi,

    i have 2 tables , a table for my articles and a table for categories !

    i need to show 5 latest posts in articles for each cat in a page

    for now i get cat names by this query :
    Code:
    select c_id,c_name from _cats order by c_name
    loooooop

    Print the name : c_name

    now , i loop in the query and i get articles by this query :

    Code:
    select TOP 5 a_title from _articles where a_cat = prevquery.c_id order by a_date desc
    Print article : a_title

    end looooop

    now i need to combine these 2 queries in one query .

    i tried to combine group by and TOP but no results , any suggestion ?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What version of SQL Server?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    23
    hi dicanio, hope this helps
    if object_id('jk_Categories') is not null
    drop table jk_Categories
    go
    if object_id('jk_articles') is not null
    drop table jk_articles
    go

    create table jk_Categories(id int, name varchar(100))
    create table jk_articles(id int, title varchar(100), PostDate datetime)

    insert into jk_Categories values(1,'Books')
    insert into jk_Categories values(2,'Magazines')
    insert into jk_Categories values(3,'Journals')
    insert into jk_Categories values(4,'Periodicals')
    insert into jk_Categories values(5,'Sheets')

    insert into jk_articles values(1,'11111', getdate() + 1)
    insert into jk_articles values(1,'22222', getdate() + 2)
    insert into jk_articles values(1,'33333', getdate() + 3)
    insert into jk_articles values(1,'44444', getdate() + 4)
    insert into jk_articles values(1,'55555', getdate() + 5)
    insert into jk_articles values(1,'66666', getdate() + 6)
    insert into jk_articles values(1,'77777', getdate() + 7)
    insert into jk_articles values(1,'88888', getdate() + 8)
    insert into jk_articles values(1,'99999', getdate() + 9)

    insert into jk_articles values(2,'11111', getdate() + 1)
    insert into jk_articles values(2,'22222', getdate() + 2)
    insert into jk_articles values(2,'33333', getdate() + 3)
    insert into jk_articles values(2,'44444', getdate() + 4)
    insert into jk_articles values(2,'55555', getdate() + 5)
    insert into jk_articles values(2,'66666', getdate() + 6)
    insert into jk_articles values(2,'77777', getdate() + 7)

    insert into jk_articles values(3,'11111', getdate() + 1)
    insert into jk_articles values(3,'22222', getdate() + 2)
    insert into jk_articles values(3,'33333', getdate() + 3)
    insert into jk_articles values(3,'44444', getdate() + 4)

    insert into jk_articles values(4,'11111', getdate() + 1)
    insert into jk_articles values(4,'22222', getdate() + 2)
    insert into jk_articles values(4,'33333', getdate() + 3)
    insert into jk_articles values(4,'44444', getdate() + 4)
    insert into jk_articles values(4,'55555', getdate() + 5)

    insert into jk_articles values(5,'11111', getdate() + 1)
    insert into jk_articles values(5,'22222', getdate() + 2)
    insert into jk_articles values(5,'33333', getdate() + 3)
    insert into jk_articles values(5,'44444', getdate() + 4)
    insert into jk_articles values(5,'55555', getdate() + 5)
    insert into jk_articles values(5,'66666', getdate() + 6)
    insert into jk_articles values(5,'77777', getdate() + 7)
    insert into jk_articles values(5,'88888', getdate() + 8)
    insert into jk_articles values(5,'99999', getdate() + 9)


    select * from jk_Categories
    select * from jk_Articles

    select a1.id,c.name,a1.title,a1.PostDate from jk_Articles a1
    inner join jk_Categories c
    on (a1.id = c.id)
    where a1.PostDate in (select top 5 a2.PostDate from jk_Articles a2
    where a1.id = a2.id
    order by a2.PostDate desc)

  4. #4
    Join Date
    Nov 2008
    Posts
    17
    georgev , it is ms sql 2000.
    kjambu , i ll check it and let you know if my problem solved !

  5. #5
    Join Date
    Nov 2008
    Posts
    23
    2000 ?@!#*?

    but still looks like the code should work. if it does not pl tell me. It will take a few minutes to install 2000 and test.

    sorry about that

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey kjambu, chill out

    Your solution should work just fine in 2000
    P.S. it may be useful to use [CODE] tags around your code to format it nicely
    Code:
    See?
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2008
    Posts
    23
    lazy question georgev
    how do I use [code] tags ?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    you can type them in - as you have, plus closing tag, or use the WYSIWYG
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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