Listing Categories, Counting Articles

Databases
1

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

Can you post the output of a

SHOW CREATE TABLE [I]tbl_name[/I]

for both tables?

3

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

4

no sir, :)…

this is my query. I know its funny… :slight_smile:

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

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?