Need assistance with JOIN SQL

[paul.wi...@gmail.com]

I am attempting to create a report that shows both Primary and there Alternates however both tables I have use the same PERSON_ID identifier. Can someone assist me or give me something to try. The query below gives me output, but it only gives me Primary First Name/Last Name ect, email address ect ect and then it only shows the Alternates ID number opposed to giving me there First NAME/Last name ect ect. I know I need to use a join statement, but I have no experience with JOINS. PLEASE HELP!!

SELECT "ORG_ACCOUNT".ACCOUNT_NUMBER AS "Account Number",
"ORG_PERSON".ADDRESS_2 AS "Address",
"ORG_ACCOUNT".DODAAC AS "Dodaac",
"ORG_DODAAC".DRA AS "Dra",
"ORG_PERSON".EMAIL AS "Email",
"ORG_PERSON".FIRST_NAME AS "First Name",
"ORG_PERSON".LAST_NAME AS "Last Name",
"ORG_PERSON".LAST_TRAIN_DATE AS "Last Train Date",
"ORG_PERSON".MIDDLE_NAME AS "Middle Name",
"ORG_ALT_ACCOUNT_CUST".PERSON_ID AS "Alt Person Id",
"ORG_ORG".ORG_NAME AS "Org Name",
"ORG_ACCOUNT".PERSON_ID AS "Person Id",
"ORG_PERSON".PHONE_COM AS "Phone Com",
"ORG_PERSON".PHONE_DSN AS "Phone Dsn",
"ORG_PERSON".RANK AS "Rank"
FROM "ORG"."ORG_ACCOUNT" "ORG_ACCOUNT",
"ORG"."ORG_DODAAC" "ORG_DODAAC",
"ORG"."ORG_ORG" "ORG_ORG",
"ORG"."ORG_PERSON" "ORG_PERSON",
"ORG"."ORG_ALT_ACCOUNT_CUST" "ORG_ALT_ACCOUNT_CUST"
WHERE ( ( "ORG_PERSON".PERSON_ID(+) = "ORG_ALT_ACCOUNT_CUST".PERSON_ID )
AND ( "ORG_ORG".ORG_ID = "ORG_ACCOUNT".ORG_ID )
AND ( "ORG_PERSON".PERSON_ID = "ORG_ACCOUNT".PERSON_ID )
AND ( "ORG_ALT_ACCOUNT_CUST".PERSON_ID = "ORG_ACCOUNT".PERSON_ID )
AND ( "ORG_DODAAC".DODAAC = "ORG_ACCOUNT".DODAAC ) )
AND ( UPPER("ORG_ACCOUNT".DODAAC) LIKE UPPER(:DODAAC)
AND "ORG_DODAAC".DRA IN ( :P_DRA_ENTRIES)
AND UPPER("ORG_ACCOUNT".DODAAC_COMMODITY) = UPPER('A') )
ORDER BY "ORG_DODAAC".DRA ASC, "ORG_ACCOUNT".ACCOUNT_NUMBER ASC, "ORG_PERSON".LAST_NAME ASC, "ORG_ALT_ACCOUNT_CUST".PERSON_ID ASC

[Ed Prochak]

On Tuesday, December 11, 2018 at 11:07:17 AM UTC-5, paul.wi...@gmail.com wrote:
> I am attempting to create a report that shows both Primary and there
> Alternates however both tables I have use the same PERSON_ID identifier.
> Can someone assist me or give me something to try. The query below
> gives me output, but it only gives me Primary First Name/Last Name ect,
> email address ect ect and then it only shows the Alternates ID number
> opposed to giving me there First NAME/Last name ect ect. I know I need
> to use a join statement, but I have no experience with JOINS.
> PLEASE HELP!!
>

Hi Paul,
Did you solve this or are you still struggling?

One thing to try is to not do everything at once, but build up the query
one table at a time.

Comments on how we can help:
Please provide a description of the tables, namely
the primary keys and foreign keys at least.
Provide some sample cases of rows for each table.


Ed

[Ed Prochak]

Hi Paul,

I took a look at your joins. Now this is without knowing anything
about your tables, only looking at the query you posted. So it seems
that perhaps your outer join is backwards.

On Tuesday, December 11, 2018 at 11:07:17 AM UTC-5, paul.wi...@gmail.com wrote:
>

> SELECT "ORG_ACCOUNT".ACCOUNT_NUMBER AS "Account Number",

[]

> WHERE ( ( "ORG_PERSON".PERSON_ID(+) = "ORG_ALT_ACCOUNT_CUST".PERSON_ID )

This selects all rows of ORG_ALT_ACCOUNT_CUST, whether there is a matching
ORG_PERSON row or not. Now, assuming that there is a person that owns(?)
each alternate account, this will only return person rows that have one
or more Alt Accounts.

So I think you really want
WHERE ( ( "ORG_PERSON".PERSON_ID = "ORG_ALT_ACCOUNT_CUST".PERSON_ID(+) )

which should return all persons that match all the other criteria in your
query whether or not they have an alternate account.

But again, this is a total guess based only on your query given earlier.
Some information about the keys, foreign keys and other constraints
on those tables would be helpful for us to assist you.

Good luck.
Ed