Solved

SQL Join Query for Coldfusion/Oracle

Posted on 2014-01-31
10
441 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39824060
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 143

Expert Comment

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

Expert Comment

by:awking00
ID: 39824215
Can you provide some sample data for the three tables and your expected output from that data?
0
Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 

Author Comment

by:diecasthft
ID: 39824234
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39824264
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
 
LVL 32

Expert Comment

by:awking00
ID: 39824287
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
ID: 39824389
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 32

Expert Comment

by:awking00
ID: 39824854
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 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 39824868
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
ID: 39829647
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

724 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