Solved

SQL Join Query for Coldfusion/Oracle

Posted on 2014-01-31
10
423 Views
Last Modified: 2014-02-03
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!!!!
0
Comment
Question by:diecasthft
  • 4
  • 3
  • 3
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I suggest that you read up this article:
http://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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
PS: I am not 100% sure you actually need a FULL OUTER JOIN ...
are you aware of how that one exactly works?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you provide some sample data for the three tables and your expected output from that data?
0
 

Author Comment

by:diecasthft
Comment Utility
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;
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
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.
0
 

Author Comment

by:diecasthft
Comment Utility
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.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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;
0
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
Comment Utility
Sorry, I left out location -
select a.proj_no, a.location, 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;
0
 

Author Closing Comment

by:diecasthft
Comment Utility
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!!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now