Search code examples
sqlselectnestedderived-table

Why do I get a duplicate column name error only when I SELECT FROM (SELECT)

I imagine this is a really basic oversight on my part but I have an SQL query which works fine. But I when I SELECT from that result (SELECT FROM (SELECT)) I get a 'duplicate column' error. There are duplicate column names, for sure, in two tables where I compare them but they do not cause a problem in the initial result. For example:

SELECT _dia_tagsrel.tag_id,_dia_tagsrel.article_id, _dia_tags.tag_id, _dia_tags.tag
FROM _dia_tagsrel
JOIN _dia_tags
ON _dia_tagsrel.tag_id = _dia_tags.tag_id

Works fine but when I try to select from it, I get the error:

    SELECT DISTINCT tag FROM
(SELECT _dia_tagsrel.tag_id,_dia_tagsrel.article_id, _dia_tags.tag_id, _dia_tags.tag
    FROM _dia_tagsrel
    JOIN _dia_tags
    ON _dia_tagsrel.tag_id = _dia_tags.tag_id) a

Regardless of the DISTINCT. Ok, I can change the column names to be unique but the question really is - why do i get the error when I SELECT FROM (SELECT) and not in the initial query? Thanks

Solution: 

SELECT DISTINCT tag_id, tag FROM (SELECT  _dia_tagsrel.tag_id, _dia_tagsrel.article_id,  _dia_tags.tag
FROM _dia_tagsrel
JOIN _dia_tags
ON _dia_tagsrel.tag_id = _dia_tags.tag_id) a

I only needed to SELECT one of the duplicate columns, even though I was comparing the both of them. Provided by answer below.

Solution

  • Your first query returns four columns:

    • tag_id
    • article_id
    • tag_id
    • tag

    Duplicate column names are allowed in a result set, but are not allowed in a table -- or derived table, view, CTE, or most subqueries (an exception are EXISTS subqueries).

    I hope you can see the duplicate. There is no need to select tag_id twice, because the JOIN requires that the values are the same. So just select three columns:

    SELECT tr.tag_id, tr.article_id, t.tag
FROM _dia_tagsrel tr JOIN
     _dia_tags t
     ON tr.tag_id = t.tag_id