Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Joining 3 Tables

 
H Melua
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
H Melua
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks guys very much

Vigas, yours gives me this error


thanks again
 
H Melua
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1840
Eclipse IDE Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...)
 
Joel McNary
Bartender
Posts: 1840
Eclipse IDE Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, And leave the "LEFT" out. Access only likes "OUTER JOIN"
 
H Melua
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried it on postgresql and Oracle and the query works fine over there. Do you need the outer joins ?
 
H Melua
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic