WHERE clause - help please!

[BoarderLine]

I have 3 tables as follows:

 

+-----------+--------------+

table1> categories

+-----------+--------------+

 

| CatID      | category     

 

+-----------+--------------+

 

|      1      | category1  |

 

|      2      | category2  |

 

|      3      | category3  |

 

|      4      | category4  |

 

etc..

 

+-----------+--------------+

 

+-----------+--------------+--------------+

table2> cat_type_link

+-----------+--------------+--------------+

 

| linkID      |    Link_CatID      |  CatType   

 

+-----------+--------------+--------------+

 

|      1      |        1        |      1

 

|      2      |        1        |      2

 

|      3      |        2        |      1

 

|      4      |        3        |      2

 

|      4      |        4        |      1

 

etc..

 

+-----------+--------------+--------------+

 

+-----------+--------------+--------------+

table3> posts

+-----------+--------------+--------------+

 

| postID  | Post_CatID  | status 

 

+-----------+--------------+--------------+

 

|      1      |        1        |        0

 

|      2      |        2        |        1

 

|      3      |        2        |        1

 

|      4      |        1        |        1

 

|      3      |        3        |        1

 

|      4      |        1        |        1

 

etc..

 

+-----------+--------------+--------------+

 

I am trying to get the count() result for the number of posts (table posts) within each category which has a cat_type_link.CatType value of 1 and posts.status=1.

 

The required result I am needing from the above example would output :

 

category1 (# of posts 2)

 

category2 (# of posts 2)

 

category4 (# of posts 0)

 

However my query below outputs:

 

category1 (# of posts 2)

 

category2 (# of posts 2)

 

As you can see the empty count() on category4 is missing.  If I remove the WHERE posts.status=1 part of the WHERE clause the result is what I require (however I don't want a count of posts that don't have a status of 1). It seems this is because the WHERE clause is over riding the LEFT JOIN.  Do I need a sub SELECT in the query to achieve this?  Thanks heaps for anyone that may offer advice.

 

Here's what i've currently got:

 

SELECT count(posts.postID) AS count, categories.CatID, categories.category, cat_type_link.CatType 
FROM ((cat_type_link RIGHT JOIN categories ON categories.CatID = cat_type_link.Link_CatID)
LEFT JOIN posts ON posts.Post_CatID=categories.CatID)
WHERE cat_type_link.CatType = '1' AND posts.status = '1'
GROUP BY categories.CatID 
ORDER BY categories.category ASC

 

[BoarderLine]

Or is it a SELECT within the COUNT i need?

[BoarderLine]

Ok got it, thanks anyway.  Needed an additional GROUP BY field.

 

SELECT count(posts.postID) AS count, categories.CatID, categories.category, cat_type_link.CatType
FROM ((cat_type_link RIGHT JOIN categories ON categories.CatID = cat_type_link.Link_CatID)
LEFT JOIN posts ON posts.Post_CatID=categories.CatID)
WHERE cat_type_link.CatType = '1' AND posts.status = '1'
GROUP BY categories.CatID, cat_type_link.CatType
ORDER BY categories.category ASC

[BoarderLine]

No, turns out it is still not returning all categories 

[kickstart]

Hi

 

Think something like this is what you want.

 

SELECT a.CatID, a.category, b.CatType , COUNT(c.postID) postcount
FROM categories a
INNER JOIN cat_type b ON a.CatID = b.CatId AND b.CatType = 2
LEFT OUTER JOIN posts c ON b.CatId = c.Post_CatId AND c.status = 1
GROUP BY a.CatID, a.category, b.CatType 
ORDER BY postcount

 

This is excluding the unwanted records (ie cattype != 2 or status != 1) in the ON clauses

 

Not tested so please excuse any typos.

 

All the best

 

Keith

[BoarderLine]

Kickstart, you my friend are a legend.  Thanks so much.  Working perfect.  Also taught me a lot here.  Have a good one.

[BoarderLine]

Another question for this query.  Is it possible to do a count on the number of rows which do not result in a 0 post count? Or would this need to be done in a separate query?

 

[kickstart]

Hi

 

Couple of ways to do it but can't see a way to do it without a separate query.

 

Simplest

 

SELECT COUNT(*)
FROM (SELECT a.CatID, a.category, b.CatType , COUNT(c.postID) postcount
FROM categories a
INNER JOIN cat_type b ON a.CatID = b.CatId AND b.CatType = 2
LEFT OUTER JOIN posts c ON b.CatId = c.Post_CatId AND c.status = 1
GROUP BY a.CatID, a.category, b.CatType) AS subselect
WHERE postcount > 0

 

However if you wanted the other rows it might be easiest to just count the rows with a postcount of 0 while looping around them.

 

All the best

 

Keith

[BoarderLine]

Ok Keith thanks for your assist.

[BoarderLine]

What if for this same query (as per previous post) I was wanting to find the count() result where rsCount = 2 OR c.status = 1.

 

That really seems to put a spanner in things 

[BoarderLine]

Correction sorry :

 

Should be:

What if for this same query (as per previous post) I was wanting to find the count() result where b.CatType = 2 OR c.status = 1.

 

not:

What if for this same query (as per previous post) I was wanting to find the count() result where rsCount = 2 OR c.status = 1.

[kickstart]

Hi

 

Struggling to be sure of what you want there. Do you just want a count of records with those conditions you just mentioned, or do you want a count of counts again.

 

All the best

 

Keith