Can't join two temp tables

Question

I've been trying to join two temp tables but I keep getting an error message saying "Ambiguous column name" even though I used column alias. FYI, this is SQL Server programming.

select ID, count(PageNum) as Frequency 
into temp_list
from ID_USA
group by ID

select ID, ISNULL(PageNum1,'') +','+ISNULL(PageNum2,'') +','+ISNULL(PageNum3,'') +','+ISNULL(PageNum4,'') +','+ISNULL(PageNum5,'') As PageNum
from temp_table k 
left join temp_list c on c.ID = k.ID

The error message says Ambiguous column name 'ID'.

Could anyone please help? Thanks.

score 3 · Answer 1 · answered Sep 19 '20 at 02:13

Column id is there in both tables, so an unqualified id is ambiguous in the query - you have that in your select clause.

Consider prefixing this column with a table alias - since you have a left join, I picked the left table:

select k.ID, 
    concat_ws(',', c.pageNum1, c.PageNum2, c.pageNum3, c.pageNum4, c.pageNum5) As PageNum
from temp_table k 
left join temp_list c on c.ID = k.ID

I also prefixed all pageNum columns, based on the assumption that they come from the right table.

Note that I changed the + string concatenation to concat_ws(): although this does not do exactly the same thing as your original expression, this is close enough (and much more concise) to be worth a suggestion.

score 0 · Answer 2 · answered Sep 19 '20 at 12:30

First, there is no reason to use a temporary table, at least for the first query.

Second, as GMB pointed out, you need qualified column names.

Third, you can use the concat() function to exactly replicate your logic more simply (because concat() ignores NULL values).

So, I would suggest:

with temp_list as (
      select ID, count(PageNum) as Frequency 
      from ID_USA
      group by ID
     )
select k.ID,
       concat(c.PageNum1, ',', c.PageNum2, ',', c.PageNum3, ',', c.PageNum4, ',', c.PageNum5) as PageNum
from temp_table k left join
     temp_list c
     on c.ID = k.ID;

Note that every column reference in the query is preceded by the table alias. Such qualified column references are never ambiguous.

Of course, you can keep temp_list if you need it for other reasons. But you don't need a temporary table for this query.

Can't join two temp tables

2 Answers2