Welcome Guest, Not a member yet? Register   Sign In
Problem with LEFT JOIN
#1

[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


Messages In This Thread
Problem with LEFT JOIN - by El Forum - 04-19-2011, 02:52 PM
Problem with LEFT JOIN - by El Forum - 04-21-2011, 02:26 AM



Theme © iAndrew 2016 - Forum software by © MyBB