homepage Welcome to WebmasterWorld Guest from 54.204.182.118
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Join across 3 tables
Joining table 1 and 3 via table 2
migthegreek

5+ Year Member



 
Msg#: 4239270 posted 1:39 pm on Dec 6, 2010 (gmt 0)

I'm writing a query that selects from 4 tables. Here is a summary of the important columns (Most of the information comes from Table 1):

Database 1 - Table 1 (instalments)
order_id
writer (id)

Database 1 - Table 2 (orders)
order_id
customer (id)

Database 1 - Table 3 (writers)
id
fname
sname

Database 2 - Table 4 (customers)
id
fname
sname

So firstly I have the order_id from Table 1.
Then I need to get the customer for that order_id from Table 2.
Then I need to get the customer's first name and surname from Table 4, in another database, based on the customer ID from the join on Table 2.
Lastly, I also need to join the writer's first name and surname on Table 1's writer ID.

Not really sure about stacking up joins like this, especially as it's across 2 different databases.

Any help appreciated, thanks.

 

Maurice



 
Msg#: 4239270 posted 4:54 pm on Dec 7, 2010 (gmt 0)

very quickly of the top of my head using generic sql somthing like this

SELECT a.order_id, b.customer,c.fname,c.sname, d.fname,d.sname
FROM db1.tbl_1 a, db1.tbl_2 b,db1.tbl_3 c, db2.tbl_4 d
WHERE a.order_id = b.order_id AND
b.customer = d.id AND
a.writer = c.id

though you need to say what database you are.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved