I'm fairly new to SQL and have done some joins before but nothing as complex as this.
Basically, what I'm trying to do is to join together many tables to show overall data for reporting. The main table g, is where I would like to see the level of detail at. g has a One-to-many relationship with all tables attached to it. Would a GROUP BY eliminate these duplicates?
I've tried using SELECT DISTINCT, but I'm not sure if that's the best solution.
Here is the output of the SQL that I'm currently running ORA-00904: "a"."akey": invalid identifier.
SELECT
a.afield123 ORDER_QTY,
b.bfield123 MANUF,
c.cfield123 ORDER_DATE,
d.dfield123 PATIENT_CREATE_DATE,
e.efield123 PAYOR_CLASS,
f.ffield123 ID
FROM
schema123.a12345 a,
schema123.b12345 b,
schema123.c12345 c,
schema123.d12345 d,
schema123.e12345 e,
schema123.f12345 f,
LEFT JOIN schema123.g12345 g ON
(a.akey = b.akey AND
c.ckey = d.ckey AND
e.ekey = f.ekey)
Here's some sample data
G Table (Main Table)
Primary Key: gfield123
Foreign Keys: akey,bkey,ckey,dkey,ekey,fkey,hkey
gfield123 gfield124 akey bkey ckey dkey ekey fkey hkey
1 8/1/2017 1234 2234 3234 4234 5234 6234 7234
A Table
Primary Key: akey
Foreign Keys: gfield123, bkey
akey afield123 gfield123 bkey
1234 20 1 2234
1235 40 1 2234
B Table
Primary Key: bkey
Foreign Keys: gfield123, ckey, akey
bkey bfield123 gfield123 akey ckey
2234 COMPANY1 1 1234 3234
C Table
Primary Key: ckey
Foreign Keys: gfield123, bkey, dkey
ckey cfield123 gfield123 bkey dkey
3234 8/5/2017 1 2234 4234
D Table
Primary Key: dkey
Foreign Keys: gfield123, ckey, ekey
dkey dfield123 gfield123 ckey ekey
4234 7/1/2017 1 3234 5234
E Table
Primary Key: ekey
Foreign Keys: gfield123, fkey
ekey efield123 gfield123 fkey
5234 MEDICARE 1 6234
F Table
Primary Key: fkey
Foreign Key: gfield123
fkey ffield123 gfield123
6234 ABC123 1
H Table
Primary Key: hkey
hkey hfield123
7234 9999
Expected Outcome
gfield123 afield123 bfield123 cfield123 efield123 ffield123 hfield123
1 60 COMPANY1 8/5/2017 MEDICARE ABC123 9999
edit: SQL simplification, Data Samples, Expected Outcome