I'm having a mind blank - ordering when multiple links

Discussion in 'PHP' started by Triexa, Aug 27, 2008.

0
  1. #1
    I have 3 tables (simplified for example):

    entries:
    entry_id | cat_id | entry_date | entry_body

    categories:
    cat_id | cat_name | cat_level

    category_links:
    cat_id | entry_id


    I want to select entries ordered by their entry_date in DESC order, and also grab the category they appear in. They can appear in multiple categories, so I want to grab the one with the highest cat_level. How do I do this?

    Thanks!
     
    Triexa, Aug 27, 2008 IP
  2. bartolay13

    bartolay13 Active Member

    Messages:
    735
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    98
    #2
    is this a one row result?
     
    bartolay13, Aug 27, 2008 IP
  3. Triexa

    Triexa Active Member

    Messages:
    580
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #3
    nope, many rows of entries, ordered by entry_date desc...
     
    Triexa, Aug 27, 2008 IP
  4. bartolay13

    bartolay13 Active Member

    Messages:
    735
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    98
    #4
    my advise is to use sub query

    select t1.entry_id,(select t2.cat_id from categories as t2 left join entries as t1 on t1.cat_id = t2.cat_id order by cat_level desc limit 0) from entries as t1 left join categories from t2 on t1.cat_id = t2.cat_id

    try join the 2 tables first on respect to what you want.
    i hope it works..
     
    bartolay13, Aug 27, 2008 IP