SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict rabbitdog's Avatar
    Join Date
    Jul 2001
    Location
    So. Tenn.
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table Join troubles

    Hi,

    I'm having a heck of a time with a query and was hoping for some insight.

    Here's what I have, and what I'm trying to do:

    2 tables - news_categories, news_articles. Each category can have any number of articles.

    I need to retrieve all the categories from the categories table that contain articles.

    This is my query so far:

    PHP Code:
    select news_categories.*,count(news_articles.catid) as count from news_categories LEFT JOIN news_articles on news_articles.catid=news_categories.catid where count>AND news_articles.issueid='{$issue['issueid']}' group by news_articles.catid order by news_categories.catorder 
    The error I'm getting is: Unknown column 'count' in 'where clause'

    Any ideas?

    TYIA

    - liz.
    Mr Vector
    High quality, royalty free, vector graphics
    for t-shirt artists and graphic/web designers.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are a number of things wrong, and i will try to outline them briefly

    whenever you have a GROUP BY clause, it must include every single non-aggregate column in the SELECT list -- in this case, you have to mention every column in the news_categories table, and no, you cannot use the asterisk, so you should not use it in the SELECT list either, and thus make it easy to cut & paste

    you should not use a column alias like count because that's a reserved word and using reserved words in syntax will surely result in krptzlgrwskzxfp some day

    if you're going to go to the trouble of having a LEFT JOIN, you are defeating the purpose by restricting the count of matched rows to be greater than zero -- you might as well use an INNER JOIN instead, and then you can dispense with the >0 test, which belongs in the HAVING clause anyway, not the WHERE clause

    hope that helps

    rudy

  3. #3
    SitePoint Addict rabbitdog's Avatar
    Join Date
    Jul 2001
    Location
    So. Tenn.
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, r937, figured it out:

    select news_categories.* from news_categories,news_articles where news_articles.catid=news_categories.catid and news_articles.issueid='{$issue['issueid']}' group by news_articles.catid order by news_categories.catorder
    Mr Vector
    High quality, royalty free, vector graphics
    for t-shirt artists and graphic/web designers.


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
  •