I'm having a hard time doing what I want to do here, as SQL (and Teradata SQL on top of that) is not my main forte.
I have two queries that I want to join together. I want everything that returns from this query:
SELECT a01.fieldOne Q_One, a01.fieldTwo Q_Two, a02.fieldThree Q_Three FROM db.table_one a01, db.table_two a02 WHERE a01.fieldKey = a02.fieldKey;
This runs fine, but I need to ALSO see the results from this query at the same time:
SELECT a03.fieldFour Q_Four FROM db.table_one a01, db.table_two a02, db.table_three a03 WHERE a01.fieldKey = a02.fieldKey and a01.fieldKey = a03.fieldKey and a02.fieldKey = a03.fieldKey;
Right now, I have the query written like this:
SELECT a01.fieldOne Q_One, a01.fieldTwo Q_Two, a02.fieldThree Q_Three, a03.fieldFour Q_Four FROM db.table_one a01, db.table_two a02, db.table_three a03 WHERE a01.fieldKey = a02.fieldKey and a01.fieldKey = a03.fieldKey and a02.fieldKey = a03.fieldKey;
I understand what I'm doing wrong - I'm using an inner join, so I'm returning only results where all the
fieldKey fields match between
a01, a02 and
a03, severely limiting my results because
table_three only has a handful of items in it, so most of the items that exist in
table_two do not exist in
table_three. I want the items from
table_three that don't match, as well as the ones that do match - I'm not sure how to do this though. I believe it's a
LEFT OUTER JOIN - so I take everything from the "left", and join to it some things from the "right"? I don't know, I'm guessing here...
I'm not sure how to write this code, especially in Teradata which seems to have very sparse information around the web.
The reason I asked about using
WHERE vs using
JOIN ON is because the actual query I have is much more complicated and I'm not sure how to use the 'JOIN ON' logic with THIS many tables:
SELECT a119.a a, a11.b b, a11.c c, a18.d d, a18.e e, a11.f f, a11.g g, a11.h h, a11.i i, a11.j j, a12.k k, a124.l l, a18.m m, a18.n n, a18.o o, a111.p p, a110.q q, SUBSTR(a50.r,8,2) r, a50.s s, a127.t t, a127.u u, a127.v v, a127.w w FROM db.table1 a11, db.table2 a12, db.table3 a13, db.table4 a15, db.table5 a18, db.table6 a50, db.table7 a51, db.table8 a52, db.table9 a53, db.table10 a54, db.table11 a110, db.table12 a111, db.table13 a114, db.table14 a115, db.table15 a116, db.table16 a117, db.table17 a119, db.table18 a120, db.table19 a121, db.table20 a122, db.table21 a123, db.table22 a124, db.table23 a125, db.table24 a126 WHERE a11.someKey = a12.someKey and a11.someKey = a125.someKey and a11.someKey = a15.someKey and a125.someKey = a126.someKey and a11.someKey = a18.someKey and a11.someKey = a13.someKey and a11.someKey = a13.someKey and a18.someKey = a50.someKey and a50.someKey = a51.someKey and a51.someKey = a52.someKey and a52.someKey = a53.someKey and a53.someKey = a54.someKey and a11.someKey = a114.someKey and a11.someKey = a119.someKey and a12.someKey = a110.someKey and a12.someKey = a111.someKey and a114.someKey = a115.someKey and a11.someKey = a116.someKey and a116.someKey = a117.someKey and a11.someKey = a13.someKey
The above query (when populated with actual field names and key names) runs just fine, because the keys in question exist in ALL of the tables.
What I expect to see when joining in that LAST table (
a127) is blank fields (or null fields) for 97% of them. Is there no way for me to just put brackets or something around THIS query, run it, then join the results to the second query?