File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Joining 3 Tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Joining 3 Tables" Watch "Joining 3 Tables" New topic
Author

Joining 3 Tables

H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Hi guys
I'm trying to join 3 tables together, i only managed to join 2 tables

Table Items holds 3 columns ItemCode, ItemExpiryDate, ItemEntryDate
Table SoldITems holds 2 columns ItemCode, SoldDate
Table OutOfOrder holds 2 columns ItemCode, OOODate

so far i joined the Items table with the SoldItems


Now i want to join the Items table with the OutOfOrder Table, can some one show me how to do that?

thank you
Hannah
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
One way is to write a inline view


[added line breaks so page doesn't scroll right]
[ January 10, 2006: Message edited by: Jeanne Boyarsky ]
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
You don't have to add the complexity of an inline view
If it is an outer join you can do


If it is a normal join you can do


<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Thanks guys very much

Vigas, yours gives me this error


thanks again
H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Pure
yours gives me this error


its very hard for me to spot where its going wrong because i'm not very familiar with the join operator
thank you
Hannah
Joel McNary
Bartender

Joined: Aug 20, 2001
Posts: 1821

Accesss (and the Microsoft JET engine in general) has this odd thing about multiple joins. You have to have the parentheses in the right place. Try:



(I think I've got the parentheses correct...)


Piscis Babelis est parvus, flavus, et hiridicus, et est probabiliter insolitissima raritas in toto mundo.
Joel McNary
Bartender

Joined: Aug 20, 2001
Posts: 1821

Oh, And leave the "LEFT" out. Access only likes "OUTER JOIN"
H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Hello Joel
thanks alot, but yours also gives an error



God i'm so stressed, i've been like that for the past three days, and nothing is solved
Hannah
[ January 11, 2006: Message edited by: H Melua ]
H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Originally posted by Joel McNary:
Oh, And leave the "LEFT" out. Access only likes "OUTER JOIN"


Access does like the 'LEFT', the code i put earliar works fine!
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
I tried it on postgresql and Oracle and the query works fine over there. Do you need the outer joins ?
H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
well i need all the items in the Items table to be there and all the matching items in the other 2 tables...

MS Access is terrible, i'm only using it because thats the only one i have (!) and its too late to change now to Oracle!

thanx alot anyway
 
wood burning stoves
 
subject: Joining 3 Tables