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
#2

[eluser]temon[/eluser]
in Four Table
categoriesSelection
categorySelectionID - categoryUrl - articleID

and

in Second Table
Comments
commentID - Comment - commentUrl

1. I suggest you using categoryID for replace categoryUrl in categoriesSelection table,
articleID for replace commentUrl in Comments table.

2. you just select from categoriesSelection table and joint other table.

try it Big Grin




Theme © iAndrew 2016 - Forum software by © MyBB