Count usage - 3 tables ?

[Rommeo]

I have 3 tables ;

Category

Subcategory

Posts

 

Posts are under subcategories which are under categories.

 

Example :

Category :

id | category

7  | Vehicles

8 | Electronics

 

Subcategory :

id | subcat | catid

3  | Car      | 7

4  | Phone | 8

 

Posts :

id | topic | subcatid

1 | BMW |  3

2 | Audi  | 3

 

And I want a result like

 

Vehicles

- Car ( 2 Posts )

Electronics

- Phone ( 7 Posts )

 

I could not write a query for this, The query I wrote just returns 1 row.

What can be the query of this ?

[fenway]

You just need to join twice, and count the number of posts.

[Rommeo]

I know,

my query is as follows :

$query = "

SELECT c.*,sc.*,p.*, COUNT(p.*)

FROM category c,subcategory cs,posts p

WHERE

p.subcatid = sc.id AND

sc.id = c.id"

 

and this does not work, just returns the first row.

Whats wrong with my query ?

[DavidAM]

You need to GROUP BY category, sub-category. Add this line to the end of that query:

 

GROUP BY c.id, sc.id

[Muddy_Funster]

I know,

my query is as follows :

$query = "

SELECT c.*,sc.*,p.*, COUNT(p.*)

FROM category c,subcategory cs,posts p

WHERE

p.subcatid = sc.id AND

sc.id = c.id"

 

and this does not work, just returns the first row.

Whats wrong with my query ?

you're using select * three times (once is bad enough, but three times in a single query is inexcusable) 

you are aliasing table names for no good reason

you don't have a single JOIN in your FROM clause even though you are supposed to have two of them

 

other than that, it looks ok 

[Rommeo]

you're using select * three times (once is bad enough, but three times in a single query is inexcusable) 

you are aliasing table names for no good reason

you don't have a single JOIN in your FROM clause even though you are supposed to have two of them

 

other than that, it looks ok 

I m really not good in this query things.

I would be glad, if you could explain me what you mean ?

"you are aliasing table names for no good reason" ?

"you don't have a single JOIN in your FROM" ?

[TOA]

"you are aliasing table names for no good reason" ?

 

You're using aliases when you don't need to

 

"you don't have a single JOIN in your FROM" ?

 

You don't have any joins in there

 

Try this sql:

SELECT COUNT(id) AS CNT 
FROM Posts
INNER JOIN Subcategory ON Posts.subcatid = Subcategory.id
INNER JOIN Category ON Subcategory.catid = Category.id
WHERE Category.id=$your_desired_category_id

 

Not tested but should get you started

[Rommeo]

You don't have any joins in there

 

Try this sql:

SELECT COUNT(id) AS CNT 
FROM Posts
INNER JOIN Subcategory ON Posts.subcatid = Subcategory.id
INNER JOIN Category ON Subcategory.catid = Category.id
WHERE Category.id=$your_desired_category_id

 

Not tested but should get you started

Actually I want to show the all categories and sub categories and the count of posts like :

 

Vehicles

- Car ( 2 Posts )

Electronics

- Phone ( 7 Posts )

 

I need to SELECT more I guess :S ?

and there is one thing that I did not understand,

what is this CNT for ?

"SELECT COUNT(id) AS CNT " ?

[TOA]

CNT stood for count, which was the one alias you would use to echo it later, like:

while ($row = mysql_fetch_assoc($your_result)) { echo "$row[CNT]"; }

 

I said it would get you started. Just showing an example of the type of join you need. If you ran it with the category you wanted it would return the count. Loop it and you might be close.

 

Try and then show us your code

[Rommeo]

it does not work, returns just one row

[TOA]

it does not work, returns just one row

 

Can you post your code?