[eluser]Miguel Diaz[/eluser]
Hi I am new using codeigniter and I starting a blog from scratch, I have 4 tables
First Table Articles
articleID - articleTitle - articleUrl
Second Table Comments
commentID - Comment - commentUrl
Third Table Categories
categoryID - categoryName - categoryUrl
Four Table
categoriesSelection
categorySelectionID - categoryUrl - articleID
I am trying to us a leftjoin to union all the tables this one works perfect taking the category of each article
Code:
SELECT
a.articleID as 'articleID',
GROUP_CONCAT(CONCAT('</a href=\"./',cat.categoryUrl,'\">', cat.categoryUrl, '</a>')) as categories
FROM articles a
LEFT JOIN categoriesSeleccion cs ON a.articleID=cs.articleID
LEFT JOIN categories cat ON cs.categoryUrl=cat.categoryUrl
GROUP BY a.articleID
desc limit 5
but when I try to make a count comments start duplicating values in the categoryUrl
Code:
SELECT
a.articleID as 'articleID',
GROUP_CONCAT(CONCAT('</a href=\"./',cat.categoryUrl,'\">', cat.categoryUrl, '</a>')) as categories,
count(c.commentUrl) as Comments
FROM articles a
LEFT JOIN categoriesSeleccion cs ON a.articleID=cs.articleID
LEFT JOIN categories cat ON cs.categoryUrl=cat.categoryUrl
LEFT JOIN comments c ON a.articleUrl=c.commentUrl
GROUP BY a.articleID
desc limit 5
Please can someone help on how can I make a count in the same query with the right values I am new with left joins and I need to make this for my job. please someone enlight me
Thanks in advance