Listing Categories, Counting Articles

[boxtxod]

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,,

[SpacePhoenix]

Can you post the output of a

SHOW CREATE TABLE [I]tbl_name[/I]

for both tables?

[r937]

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

[boxtxod]

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.....

.

[r937]

first of all, you should really learn to use line breaks and indentation to make your sql more readable...

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?