CODEWITHSUNDEEP
sqlamazon-redshift
puzeledbysql
puzeledbysql

Reputation: 194

column reference is ambiguous sql error

I'm trying to join 4 different table to calculate item revenue depending on some conditions ,I will write sql query after I'm able to build the right view using 4 tables ...below is the query for the view and I'm getting ambiguous column error for ik...can someone please explain why? Thanks for your help!

select top 5 g.*
from (
    select *
    from (
        (
            select a.ik
                ,b.upc as upc
                ,b.class
                ,a.units
            from Tbl1 a
            join TBL2 b
                on a.ik = b.ik
            ) c join Tbl3 d
            on c.ik = d.ik
        ) e
    join Tbl4 f
        on e.ik = f.ik
    ) g

Upvotes: 1

Views: 5726

Answers (2)

user3600910
user3600910

Reputation: 3109

you can run select * when you have join , you should put the table alias

select top 5 g.*
from (
    select *
    from (c.*,b.*
        (
            select a.ik
                ,b.upc as upc
                ,b.class
                ,a.units
            from Tbl1 a
            join TBL2 b
                on a.ik = b.ik
            ) c join Tbl3 d
            on c.ik = d.ik
        ) e
    join Tbl4 f
        on e.ik = f.ik
    ) g

Upvotes: 3

grizzthedj
grizzthedj

Reputation: 7505

It is hard to follow what this query is doing, but since you are using sub-selects, try aliasing ik in the select part of each of the queries where it is included in the select(i.e. select a.ik as 'aliased_ik').

You should also not use .*, in the outer select query, but rather use each of the individual column names. If ik is in the outer select query, you should alias that as well.

Upvotes: 0

Related Questions