Duplicate column name SQL - need change alias?

Question

I have written SQL query with a INNER JOIN and Sub-query:

SELECT c.*, 
       ar.ArticleName, 
       ar.idArticle, 
       du.DetailToUsersName, 
       du.DetailToUsersPhoto, 
       COUNT(c.idCommentToArticle) AS CNT, 
       CASE WHEN d.Count IS NULL THEN 0 ELSE d.Count END AS CountLikes 
from (select * 
      from commenttoarticle g 
      inner join (select distinct(s.idCommentToArticle) 
                  from commenttoarticle s 
                  order by s.CommentToArticlePID limit 3) as gh) as c 

LEFT JOIN article ar ON c.CommentToArticleIdArticle = ar.idArticle 
LEFT JOIN detailtousers du ON du.idDetailToUsers = c.CommentToArticleIdUser 
LEFT JOIN `likes` d ON (d.IdNote = c.idCommentToArticle AND d.LikeType = 6) 
WHERE c.CommentToArticleIdArticle = 11 
GROUP BY c.idCommentToArticle 
ORDER BY c.idCommentToArticle DESC

So, I get error:

Duplicate column name 'idCommentToArticle'

I can not find where the duplication is?

The duplication is the result of the inner query directly after the 1st FROM. You are joining `commenttoarticle g` to the subquery which returns only idCommentToArticle. When you do `SELECT *` on this it takes all the fields from `g` and your subquery, and attempts to alias the fieldnames for the outer query `c`. This is the point where it cannot create the alias because it is in both. When you try to reference `c.idCommentToArticle` it cannot tell whether you mean from table alias `g` or the subquery. — simo.3792, Oct 27 '14 at 02:47
I would suggest that your `LIMIT` be pushed to the outer query. Putting it in your inner query means that your are limiting only to the first 3 records of the `commenttoarticle` table, irrespective of what values are in those rows. Doing this means that you are NOT using any of your filter criteria from the outer query. In designing a `LIMIT` query, you would typically filter out all the rows not required, before applying the `LIMIT`. Then `JOIN` onto matching tables to finalise the result set. — simo.3792, Oct 27 '14 at 02:55

radar · Answer 1 · 2014-10-26T23:26:51.093

2

you can specify in the alias table query c

select g.* from commenttoarticle g

instead of

select * from commenttoarticle g

Also you should specify Join condition to limit the rows to 3 as per your intention, with out the ON clause it will be like a cross join.

select g.* from commenttoarticle g 
inner join (select distinct(s.idCommentToArticle) from commenttoarticle s order by s.CommentToArticlePID limit 3) as gh
     on g.idcommenttoarticle = gh.idcommenttoarticle

edited Oct 26 '14 at 23:26

answered Oct 26 '14 at 23:16

radar

13,270
2
25
33

@allendegrud, add join condition like I mentioned – radar Oct 26 '14 at 23:25
Yes, it is a right answer. But if I specify `Join condition`, I dont get rows, where `PID > 0`, only first 3 rows `WHERE PID = 0`, So, I need get first rows PID and PID'S rows. – AllenDegrud Oct 26 '14 at 23:35
I answered about `PID, ID` here: http://stackoverflow.com/questions/26575283/why-operator-limit-does-not-work-correctly But nobody could not help me – AllenDegrud Oct 26 '14 at 23:37
@AllenDegrud add pid> 0 also to this limit query itself – radar Oct 27 '14 at 00:20
I need get latest parents rows with a all child's rows, limited by parent's rows. For example: there are rows with **ID**: `1,2,3,4,5`. `ID = 3` have a children's `3.1, 3.2`. So, If I use LIMIT 4, I need get: `1, 2, 3, 3.1, 3.2, 4`. – AllenDegrud Oct 27 '14 at 00:24
@AllenDegrud, can you create sql fiddle and show us expected output – radar Oct 27 '14 at 00:29

score 0 · Accepted Answer · edited May 23 '17 at 12:05

As @RADAR has suggested, your inner query joins don't seem to be complete. And I see from comments that once you place the JOIN condition in, then you lose all data. I think this is because neither part of the subqueries were doing what they were supposed to do.

Here is my attempt at a total solution (note, without dataset and table definition I can't show it working). OK, so you have asked the question again over here and provided a SQL-Fiddle, I have updated with a working version, but minus the additional JOIN tables, since they are not defined.

SELECT c.*, 
       ar.ArticleName, 
       ar.idArticle, 
       du.DetailToUsersName, 
       du.DetailToUsersPhoto, 
       COUNT(c.idCommentToArticle) AS CNT, 
       CASE WHEN d.Count IS NULL THEN 0 ELSE d.Count END AS CountLikes 
FROM commenttoarticle c   -- one layer of subquery not required.
INNER JOIN (select  s.idCommentToArticle, s.CommentToArticlePID -- added both the id and the parent id
            FROM commenttoarticle s 
            WHERE s.CommentToArticleIdArticle = 11 -- moved to inner query, instead of outer query
            ORDER BY s.idCommentToArticle DESC limit 3) as gh 
        ON c.idcommenttoarticle = gh.idcommenttoarticle   -- add join condition
        OR c.idcommenttoarticle = gh.CommentToArticlePID  -- which matches id and parent id

LEFT JOIN article ar ON c.CommentToArticleIdArticle = ar.idArticle 
LEFT JOIN detailtousers du ON du.idDetailToUsers = c.CommentToArticleIdUser 
LEFT JOIN `likes` d ON (d.IdNote = c.idCommentToArticle AND d.LikeType = 6) 

GROUP BY c.idCommentToArticle 
ORDER BY c.idCommentToArticle DESC

But let me explain a little further, the following code from your original query was selecting the top 3 idCommentToArticlePID,

(select * 
  from commenttoarticle g 
  inner join (select distinct(s.idCommentToArticle) 
            from commenttoarticle s 
            order by s.CommentToArticlePID limit 3) as gh)

but then because there was no ON specified the 3 records were then joined to every single record from the g reference. This resulted in the full dataset being returned.

And then you you specified WHERE c.CommentToArticleIdArticle = 11 this filtered the result set back down again to something that looked correct.

When you then added the ON (as per @RADAR's suggestion) the inner query did not contain any values that matched the WHERE c.CommentToArticleIdArticle = 11 filter and thus you lost all your results. If you move this filter into the inner query as shown above, then these will work together and not conflict.

Within the JOIN condition, you indicate that you want both the matching articles and their parents, so I added both to the return of the inner query, and checked for either in the join condition.

Also I think the whole g table reference is redundant and can be removed. You should be able to access this table directly as c.

I also have some concerns about the GROUP BY and COUNT (c.idCommentToArticle) - there seem a little strange, but I have no supporting context (ie data examples), so they may be correct. If you still have issues, I would comment the GROUP BY and COUNT statements out, and test to see what data you are getting, before adding these back in.

Is not correct result at any type of your example. I have checked. The first query returns all rows, and with condition WHERE. — AllenDegrud, Oct 27 '14 at 23:54
@AllenDegrud - I was working on the update just then. do you want to try again? see SQL Fiddle near top of answer. — simo.3792, Oct 27 '14 at 23:57

Duplicate column name SQL - need change alias?

2 Answers2