asked on

SQL Join Query for Coldfusion/Oracle

Good Morning All!!! I was hoping to get a little assistance with a query. I'm trying to pull records from three different tables. The query I have below goes to two tables, and the idea is that I have project numbers in the CON_ACT table, and I have additional
data in the ARNG_DATA table. What I don't have is a one to one relationship with the tables as not all PROJ_NO numbers are in the ARNG_DATA table. For the ARNG_DATA table, I will never have more than one record per PROJ_NO. The full outer
join seems to work the way I wanted it to, and returns what I expect.

SELECT
DISTINCT CON_ACT.PROJ_NO,
CON_ACT.LOCATION,
CON_ACT.STATE,
CON_ACT.CAPY,
ARNG_DATA.FY_RQD
FROM MCA.CON_ACT
FULL OUTER JOIN MCA.ARNG_DATA
ON CON_ACT.PROJ_NO = ARNG_DATA.PROJ_NO
WHERE CON_ACT.COMPONENT = 'National Guard';

No problem. However I am now trying to add a third table called BUDGET_TECH_POC, and that table could possible have multiple records in it for the same PROJ_NO of the CON_ACT table. So what I want is if there are multiple records in the BUDGET_TECH_POC
table, I only want the most recent one, based on the MAX MIPR_ID number that is in the table. I tried the following:

SELECT
DISTINCT CON_ACT.PROJ_NO,
CON_ACT.LOCATION,
CON_ACT.STATE,
CON_ACT.CAPY,
ARNG_DATA.FY_RQD,
TP.TPOC
FROM MCA.CON_ACT
FULL OUTER JOIN MCA.ARNG_DATA
ON CON_ACT.PROJ_NO = ARNG_DATA.PROJ_NO
FULL OUTER JOIN (SELECT TP.PROJ_NO, MAX(TP.MIPR_ID) AS MIPRID,MIN(TP.TECH_POC_NAME) AS TPOC
FROM MCA.BUDGET_TECH_POC GROUP BY BUDGET_TECH_POC.PROJ_NO) TP
ON TP.PROJ_NO = CON_ACT.PROJ_NO
WHERE CON_ACT.COMPONENT = 'National Guard'
ORDER BY CON_ACT.PROJ_NO DESC;

And that kind of works, but the problem is that I may not be getting the TECH_POC_NAME that goes with it's corresponding MIPR_ID, since I'm using MIN. Wouldnt be a big deal if I only wanted that field, except now I want to add the TECH_POC_PHONE from the same table. When I do that, I still get a result, but it's incorrect. The TECH_POC_NUMBER may not match the TECH_POC_NAME. So what I want to do is grab the TECH_POC_NAME, and TECH_POC_PHONE of the highest numbered MIPR_ID in the BUDGET_TECH_POC table.

Thank You All So Much in Advance!!!!

Guy Hengel [angelIII / a3]

I suggest that you read up this article:
https://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
it should help to understand the "issue" and help with finding some sql solutions

Guy Hengel [angelIII / a3]

PS: I am not 100% sure you actually need a FULL OUTER JOIN ...
are you aware of how that one exactly works?

awking00

Can you provide some sample data for the three tables and your expected output from that data?

diecasthft

ASKER

I went through the post above, and based on that, this is more involved than my knowledge at this point. I don't do a ton of this kind of work so there's alot I don't know. After reading the post, and looking at the examples, I found and modified the below. It gives me what I want, from the standpoint of the name, number, ect matching the MIPR_ID, and PROJ_NO from both tables, but what it isn't giving me is all of the records from the CON_ACT table, which is my base table. So it looks like it's only giving me results where an existince of PROJ_NO is in both tables.

select E.PROJ_NO, E.LOCATION, sq.*
from ( SELECT t.*
, ROW_NUMBER() OVER ( PARTITION BY PROJ_NO ORDER BY MIPR_ID DESC ) rn
FROM MCA.BUDGET_TECH_POC t
) sq
join MCA.CON_ACT e
ON sq.PROJ_NO = e.PROJ_NO
WHERE sq.rn = 1
ORDER BY SQ.PROJ_NO DESC;

Guy Hengel [angelIII / a3]

please try this:

select E.PROJ_NO, E.LOCATION, sq.*
  from ( SELECT t.*
              , ROW_NUMBER() OVER ( PARTITION BY PROJ_NO ORDER BY MIPR_ID DESC ) rn
           FROM MCA.BUDGET_TECH_POC t
        ) sq
full outer  join MCA.CON_ACT e
    ON sq.PROJ_NO = e.PROJ_NO
  AND sq.rn = 1
 ORDER BY SQ.PROJ_NO DESC;

Open in new window

awking00

I don't see the arng_data table in your latest query so I don't know what effect that may have. If you need all records from the con_act table, you would need to left join the query to that. Again, sample data from all three tables and the desired output would help greatly and provide us something with which to test.

diecasthft

ASKER

The above query is giving me all of the records from the BUDGET_TECH_POC table. So here's kind of what I have going.

CON_ACT TABLE

ID PROJ_NO LOCATION
-----------------------------------
1      12345 LOCATION1
2      54321 LOCATION2
3      22445 LOCATION3

BUDGET_TECH_POC TABLE

TECH_POC_ID MIPR_ID      PROJ_NO TECH_POC_NAME
--------------------------------------------------
1            1      12345 NAME1
2            2      12345 NAME2
3            3      22445 NAMEA
4            4      22445 NAMEA

ARNG_DATA TABLE

ID PROJ_NO FUNDING
-----------------------------------
1      12345 FUNDING1

So the result I'm looking for is:

PROJ_NO LOCATION TECH_POC_NAME FUNDING
-------------------------------------------------------------------------
12345      LOCATION1      NAME2        FUNDING1
54321      LOCATION2
22445      LOCATION3      NAMEA

I only get one result per PROJ_NO, regardless of what is in the
BUDGET_TECH_POC or ARNG_DATA table which could have dupes of the proj_no, or perhaps the PROJ_NO is not in that table at all yet. For the TECH_POC_NAME, I want the name with the highest MIPR_ID, or the highest TECH_POC_ID number for that PROJ_NO.

awking00

select a.id, a.proj_no, b.tech_poc_name, c.funding
from con_act_table a
left join
(select mipr_id, proj_no, tech_poc_name from
(select mipr_id, proj_no, tech_poc_name,
row_number() over (partition by proj_no order by mipr_id) rn
from budget_tech_poctable)
where rn = 1) b
on a.proj_no = b.proj_no
left join arng_data c
on a.proj_no = c.proj_no
order by a.id;

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

diecasthft

ASKER

That was exactly it!!!! I'm going through it to understand what's going on so I can get a little educated on this, but it's exactly what I needed. Thanks Alot!!!