SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Listing Categories, Counting Articles

    Hi really need your help badly..

    I have 2 tables..
    categories and contents..

    1.List the categories
    2. Count the associated articles
    for example.
    Japan(2)
    Singapore (0)
    Taiwan (9)
    ........
    categories field
    id,title,isPublished
    content field
    id,title,state

    state is 1-enabled,0-disabled,-2 for deleted....


    thank you,,

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,993
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Can you post the output of a
    Code SQL:
    SHOW CREATE TABLE [I]tbl_name[/I]
    for both tables?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by boxtxod View Post
    1.List the categories
    2. Count the associated articles
    looks like a homework assignment

    we need you to make your best attempt, and then we will help you

    show us the queries that you tried
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no sir, .....

    this is my query. I know its funny..

    select distinct jcat.title,jcon.state,jcat.alias as jalias,count(catid) as count from jos_content as jcon right join jos_categories as jcat on jcon.catid=jcat.id where jcat.id>6 and jcat.published=1 and jcon.state=1 or jcon.state is null and jcat.id>6 and jcat.published=1 group by jcat.id.....

    .

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    first of all, you should really learn to use line breaks and indentation to make your sql more readable...
    Code:
    SELECT DISTINCT jcat.title
         , jcon.state
         , jcat.alias as jalias
         , COUNT(catid) as count 
      FROM jos_content as jcon 
    RIGHT 
      JOIN jos_categories as jcat 
        ON jcon.catid = jcat.id 
     WHERE jcat.id > 6 
       AND jcat.published = 1 
       AND jcon.state = 1 
        OR jcon.state is null 
       AND jcat.id > 6 
       AND jcat.published = 1 
    GROUP 
        BY jcat.id
    now, i might quibble about a number of things in your query, but it should still work

    can you describe what is wrong with this query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •