CODEWITHSUNDEEP
sqljoinsubqueryamazon-redshift
Almo Ubuy
Almo Ubuy

Reputation: 1

RedShift SQL subquery with Inner join

I am using AWS Redshift SQL. I want to inner join a sub-query which has group by and inner join inside of it. When I do an outside join; I am getting an error that column does not exist.

Query:

SELECT      si.package_weight
FROM        "packageproduct" ub "clearpathpin"  cp ON ub.cpipr_number = cp.pin_number
  INNER JOIN "clearpathpin"  cp ON ub.cpipr_number = cp.pin_number
  INNER JOIN (
    SELECT sf."AWB", SUM(up."weight") AS package_weight
    FROM "productweight" up ON up."product_id" = sf."item_id"
    GROUP BY sf."AWB"
    HAVING sf."AWB" IS NOT NULL
    ) AS si ON si.item_id = ub.order_item_id 
LIMIT 100;

Result:

ERROR: column si.item_id does not exist

Upvotes: 0

Views: 6014

Answers (2)

ScottieB
ScottieB

Reputation: 4052

There are many things wrong with this query.

For your subquery, you have an ON statement, but it is not joining:

FROM "productweight" up ON up."product_id" = sf."item_id"

When you join the results of this subquery, you are referencing a field that does not exist within the subquery:

    SELECT sf."AWB", SUM(up."weight") AS package_weight
     ...
    ) AS si ON si.item_id = ub.order_item_id

You should imagine the subquery as creating a new, separate, briefly-existing table. The outer query than joins that temporary table to the rest of the query. So anything not explicitly resulted in the subquery will not be available to the outer query.

I would recommend when developing you write and run the subquery on its own first. Only after it returns the results you expect (no errors, appropriate columns, etc) then you can copy/paste it in as a subquery and start developing the main query.

Upvotes: 0

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

It's simply because column si.item_id does not exist

Include item_id in the select statement for the table productweight

and it should work.

Upvotes: 1

Related Questions