Home » SQL & PL/SQL » SQL & PL/SQL » Combine table info
Combine table info [message #283347] Mon, 26 November 2007 18:29 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I have two Oracle 9i tables in my schema:

TableMain
MID  PO   TE   INFO
1    2    3    ABC
2    2    1    EREER
3    1    3    OIUOI
4    3    3    PERE
5    2    2    DFE


TablePerson
PID   FIRSTNAME   LASTNAME
1     Joe         Smith
2     Cal         Jones
3     Bill        Carson


I would like to get this output if I query for a MID number such as 3:
Firstname  Lastname   Firstname2 Lastname2  Info
Joe        Smith      Bill       Carson     OIUOI


My attempt below only fetches one name and ignores the TE value.

select * from TablePerson INNER JOIN TableMain on
TablePerson.PID = TableMain.PO
where TableMain.MID = 3



Another example if I query for MID number 5 I should get this:
Firstname  Lastname   Firstname2 Lastname2  Info
Cal        Jones      Cal        Jones      DFE



Please advise
Re: Combine table info [message #283348 is a reply to message #283347] Mon, 26 November 2007 18:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
TablePerson must be in the FROM clause twice
Re: Combine table info [message #283350 is a reply to message #283347] Mon, 26 November 2007 19:57 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks.

Here is how I got it to work:
SELECT TableMain.MID, TablePerson.PID, TablePerson.FIRSTNAME, TablePerson.LASTNAME, TablePerson_1.FIRSTNAME, TablePerson_1.LASTNAME, TableMain.INFO
FROM TablePerson INNER JOIN (TableMain INNER JOIN TablePerson AS TablePerson_1 ON TableMain.TE = TablePerson_1.PID) ON TablePerson.PID = TableMain.PO
WHERE TableMain.MID=3


I have a third (and final) table called NotesTable and need to include that in the query.

For example if I query for MID number 3, I should get this:
Firstname Lastname Firstname2 Lastname2 Info   Notes      
Joe       Smith    Bill       Carson    OIUOI  other data


Here is NotesTable where Notes field is sometimes null and MID is a Foreign key:
NotesID  MID   Notes 
1        1    
2        3     other data 
3        2    
4        5     miscl notes here


Please advise would I use a left join or inner join. Not sure how to do this.
Re: Combine table info [message #283358 is a reply to message #283347] Mon, 26 November 2007 22:53 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

Please advise would I use a left join or inner join. Not sure how to do this.
Yes, use LEFT or INNER join on MID column. Not sure too, as I do not know your EXACT requirements.
Are you supposed to return a row, even when there is NO row with required MID in NotesTable? Then use LEFT JOIN, otherwise use INNER join.
Why does NULL in Notes column matter? Are you always supposed to return non-empty Notes? Then look at NVL or COALESCE functions.

And just a formatting hint: one clause may be on more lines, so do not hesitate to logically break the code, as it is going to be untransparent (at least for me). Eg. the FROM clause I would format (and use shorter alisases) to
FROM TableMain TM
  INNER JOIN TablePerson TP1 ON TM.TE = TP1.PID
  INNER JOIN TablePerson TP2 ON TM.PO = TP2.PID
Previous Topic: update query problem
Next Topic: fetch out of sequence
Goto Forum:
  


Current Time: Thu Dec 08 12:42:40 CST 2016

Total time taken to generate the page: 0.09608 seconds