Trouble getting SQL left join to Execute

[bill7191]

Hi-- I'm trying to join two tables using SQL code in PQ. joining on a common field ID_NUM.  I was getting errors that the ID_NUM field was ambiguous, so I specifically defined the first with its table name alias "a" as a prefix.  The query now outputs, but does not execute the LEFT JOIN to add the fields I've called for from table b.  I am admittedly brand new to SQL, so I'm hoping there is something simple I'm overlooking, but I have spent hours trying everything I can think of or understand from online posts to no avail.  If if matters, the ID_NUM in Table "a" is not a key field, as there are multiple records with the same ID_NUM.  For Table "b" its ID_NUM is a key field, and I am trying to add the Table "b" fields to my field selections in Table "a" using a LEFT JOIN on any match between the ID_NUM fields on the two tables..  In effect I'm trying to say in the code: for each ID_NUM in "a", if there is a matching ID_NUM in "b", then add the fields in table "b" and their contents to the joined table.  Below is the specific code... if anyone can help me solve, it will be truly appreciated... thanks

"SELECT
a.ID_NUM,
YR_CDE,
TRM_CDE,
CRS_CDE,
REQUEST_NUM,
GRADE_SCALE_CDE,
CREDIT_TYPE_CDE,
CREDIT_HRS,
CRS_TITLE
FROM STUDENT_CRS_HIST a
LEFT JOIN STUDENT_DIV_MAST b ON a.ID_NUM = b.ID_NUM
WHERE YR_CDE = '2021'")

[bill7191]

Many thanks.... great suggestions 🙂

[BA_Pete · Accepted solution]

Hi @bill7191 ,

I think the code that you've provided would actually create an INNER JOIN as you've put the WHERE clause outside the join, rather than adding it as an 'AND' in the ON clause. I think you should actually write it like this:

SELECT
    a.ID_NUM,
    b.YR_CDE,
    b.TRM_CDE,
    b.CRS_CDE,
    b.REQUEST_NUM,
    b.GRADE_SCALE_CDE,
    b.CREDIT_TYPE_CDE,
    b.CREDIT_HRS,
    b.CRS_TITLE
FROM
    STUDENT_CRS_HIST a
    LEFT OUTER JOIN STUDENT_DIV_MAST b
    ON a.ID_NUM = b.ID_NUM AND b.YR_CDE = '2021'

But this isn't important, then next bit is: I'd recommend to scrap your native query all together.

Bring both tableA and tableB into Power Query and filter them as required (b.YR_CDE = 2021 etc.) then perform the merge in PQ, which will fold to your source i.e. will send the optimised SQL that you're trying to create to the source for you.

Pros:

- As you're new to SQL, you won't actually need to write any code

- You'll be able to quickly and easily visualise the source tables and the post-join table to ensure the correct results

- PQ will fold the query to the SQL source, so absolutely no drop in perfomance

- The ability to fold further transformations to the source will remain available.

Cons:

- NONE

Pete