JOINS - and a Record Count 1

[TamedTech]

Hello Guys,

I'm having issues writing a query for this feature, i'm sure its not complicated but I could use a little assistance.

Basicly i have a list of Articles, these are all sorted in my table called 'tblArticles' ... each article belongs to a catagory, the catagory details are stored in another table called 'tblArticlesCatagories' ... in the Articles table there is a field called 'Article_catagory' which refers to the Primary Key of the 'tblArticlesCatagories' table.

Now what I want to do is display a list of my catagories along with a description for each one, and next to the catagory name have a record count of articles that belong to that Catagory, so it would appear like so.

Catagory 1 (5 Articles)
-----------------------
This Catagory contains lots of informaiton on blah blah blah...

Catagory 2 (24 Articles)
-----------------------
This Catagory contains lots of informaiton on blah blah blah...


You see what i'm trying to achieve? I've played around with a few different JOINS but keep getting odd results, for instants if catagory 1 contains 3 articles then it will display the title and description for catagory 1 three times!

Any help you can offer would be greatly appreciated.

Rob

 

[r937]

select tblArticlesCatagories.descr
     , count(tblArticles.Article_catagory)
            as article_count
  from tblArticlesCatagories
left outer
  join tblArticles
    on tblArticles.Article_catagory
     = tblArticlesCatagories.id
group
    by tblArticlesCatagories.descr

r937.com | rudy.ca

 

[TamedTech]

Thanks for that Rudy ... LEFT OUTER JOIN it was, i just ammended the field names so it looks as follows.

	SELECT tblArticlesCatagories.catagory_id, tblArticlesCatagories.catagory_name, tblArticlesCatagories.catagory_description, count(tblArticles.Article_ctg) AS article_count
	FROM tblArticlesCatagories
	LEFT OUTER JOIN tblArticles ON tblArticles.Article_ctg = tblArticlesCatagories.catagory_id
	GROUP BY tblArticlesCatagories.catagory_description

And it working perfectly, thanks for your help,

Rob

 

[r937]

that gots to be mysql, because that's the only database i know of that will allow you to write an invalid GROUP BY

if you have this --

SELECT
tblArticlesCatagories.catagory_id
, tblArticlesCatagories.catagory_name
, tblArticlesCatagories.catagory_description
, count(tblArticles.Article_ctg)

then you need to use this --

GROUP BY
tblArticlesCatagories.catagory_id
, tblArticlesCatagories.catagory_name
, tblArticlesCatagories.catagory_description



r937.com | rudy.ca

 

[TamedTech]

lol .. thanks for that Rudy,

As you spotted its the fabled old MySQL ... it was working without any beef even with the dodgy GROUP BY statement, but i've ammended anyway.

Thanks again Rudy,

Rob