Home » SQL & PL/SQL » SQL & PL/SQL » Joining two Select Statements (Windows XP)
Joining two Select Statements [message #319703] Mon, 12 May 2008 13:55 Go to next message
LOgle0917
Messages: 10
Registered: February 2008
Location: Tennessee
Junior Member
Both of these work fine separately; How do I join these two Select Statements?

SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM,
MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2,
MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR,
MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX


FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON

WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR)
AND
MPI.CREATE_DT>=20070101
ORDER BY MPI.CREATE_DT

SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD
FROM PATIENT, PHYSICIAN
WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR
Re: Joining two Select Statements [message #319706 is a reply to message #319703] Mon, 12 May 2008 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean "join"?

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Joining two Select Statements [message #319707 is a reply to message #319703] Mon, 12 May 2008 14:03 Go to previous messageGo to next message
gnober
Messages: 15
Registered: May 2008
Location: NY
Junior Member
You can

<SELECT STATEMENT A>

UNION 

<SELECT STATEMENT B>


OR

<SELECT STATEMENT A>

UNION ALL

<SELECT STATEMENT B>


Above codes assume tables from SEL STATEMENT A& B have primary/foreign key relationships. You are merely adding the results of query A from the results of query B. I take it that is what you mean by "joining" them.

[Updated on: Mon, 12 May 2008 14:06]

Report message to a moderator

Re: Joining two Select Statements [message #319708 is a reply to message #319706] Mon, 12 May 2008 14:12 Go to previous messageGo to next message
LOgle0917
Messages: 10
Registered: February 2008
Location: Tennessee
Junior Member
I need information from both statements to show in one query. Both queries work fine on their own. but If I put the physician table into the first select statement it does not work because it only has a key in common with the patient table. The patient table does have a common key with the other tables in select statement "A". That is why I said join, I guess it is better to use the term nested maybe? I have never used two Selects together before so I do not know but I do know that it can be done.
Re: Joining two Select Statements [message #319719 is a reply to message #319703] Mon, 12 May 2008 15:23 Go to previous message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
You can add your second SELECT to the first one as one of the joined data sets:

SELECT ...
FROM MPI, MPI_CURRENT_ADDR, MPI_PERSON,
     (SELECT ... FROM PATIENT, PHYSICIAN WHERE ...) PAT_PHY
WHERE  (MPI.MPI_NBR = MPI_PERSON.MPI_NBR) 
  AND  (MPI.ADDR_NBR = MPI_CURRENT_ADDR.ADDR_NBR) 
  AND  (MPI.REFERENCE_ID = PAT_PHY.REFERENCE_ID)
  ...   etc.


This is exclusiionary if PAT_PHY doesn't have matches for every row in MPI. You can make sure that you keep everything from MPI with an outer join:

SELECT * FROM (
  (SELECT  ...
     FROM  MPI, MPI_CURRENT_ADDR, MPI_PERSON     
    WHERE  MPI.MPI_NBR = MPI_PERSON.MPI_NBR 
      AND  MPI.ADDR_NBR = MPI_CURRENT_ADDR.ADDR_NBR) MPI_JOIN,
  (SELECT ... 
     FROM PATIENT, PHYSICIAN 
    WHERE ...) PAT_PHY)
WHERE MPI.REFERENCE_ID = PAT_PHY.REFERENCE_ID(+)


Previous Topic: Oracle Syntax error
Next Topic: Using CASE within a CASE within a CASE - does this make sense?
Goto Forum:
  


Current Time: Fri Dec 02 20:27:39 CST 2016

Total time taken to generate the page: 0.09751 seconds