Snowflake JOIN with Multiple Tables

Question

SELECT top 10 * FROM
(SELECT * FROM DB.SCHEMA.MODULE_MASTER m
INNER JOIN DB.SCHEMA.MODULEINFO ef ON m."moduleinfoid" = ef."moduleinfoid"
INNER JOIN DB.SCHEMA.MODULE_RETURNINFO r ON m."modulemasterid" = r."modulemasterid"
INNER JOIN DB.SCHEMA.EF_TRANSACTIONS et ON m."transaction_id" = et."transaction_id"
WHERE m."platformtype" = 100 --Desktop
    AND et."firmid" <> 0 --When FirmID exists that means it's pro.
    AND r."processing_status" in (9, 20, 22, 30) -- Rejected Statuses
    AND ef."return_type" not in (1, 5, 7, 11, 18) -- Ignore Extension Types
) as table1 INNER JOIN
    ( -- Didn't have an accepted return later
        select * from DB.SCHEMA.EF_TRANSACTIONS t2
        inner join DB.SCHEMA.MODULE_MASTER mm2 on mm2."transaction_id" = t2."transaction_id"
        inner join DB.SCHEMA.MODULE_RETURNINFO ri2 on ri2."modulemasterid" = mm2."modulemasterid"
        inner join DB.SCHEMA.MODULEINFO mi2 on mi2."moduleinfoid" = mm2."moduleinfoid"
      ) as table2
on table1."moduleinfoid" = table2."moduleinfoid"

WHy is this query not working? I've verified each inner query works, but I'm not sure how to correctly alias the outer attributes to do this join. Thanks!

What do you mean by “not working”? Is it erroring - in which case what’s the error message? Does it give the wrong result - in which case what result is it giving and what did you expect it to give? — NickW, May 17 '23 at 16:30
The error: "SQL compilation error: ambiguous column name 'moduleinfoid'" — Erin, May 17 '23 at 16:42

score 0 · Answer 1 · answered May 17 '23 at 17:44

Your “table” table2 has at least 2 columns named moduleinfoid so the join condition doesn’t know which one to use.

Writing “select * …” is generally a bad practice - you should always explicitly select the columns you need and alias them as appropriate

Snowflake JOIN with Multiple Tables

1 Answers1