SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Multiple Joins to the same Table in a Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/14/2000 :  01:01:53  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Samuel writes "I wanna create a SQL query that will take each fruit1, fruit2, and fruit3 from T1 and match it with it's corresponding record in T2, and make a record set of the information . . ." Read on for the rest of Samuel's question and some interesting tricks you can do with a table alias.

Article Link.

kvahalia
Starting Member

1 Posts

Posted - 05/13/2002 :  15:03:01  Show Profile  Send kvahalia an AOL message  Send kvahalia a Yahoo! Message  Reply with Quote
i am using the same logic and have multiple joins to the same table. My sql query is

SELECT NAME,BM.ADDR,BB.ADDR,BS.ADDR
FROM S_ORG_EXT A
JOIN S_ADDR_ORG BM ON A.PR_ADDR_ID = BM.ROW_ID
JOIN S_ADDR_ORG BB ON A.PR_BL_ADDR_ID = BB.ROW_ID
JOIN S_ADDR_ORG BS ON A.PR_SHIP_ADDR_ID = BS.ROW_ID

This select statement essentially is part of my update trigger. I eventually want to monitor the addr field for update on all the three addresses (main, bill to, ship to). I envisage the final result to be something like these

SELECT NAME,BM.ADDR,BB.ADDR,BS.ADDR
FROM S_ORG_EXT A
JOIN Inserted BM ON A.PR_ADDR_ID = BM.ROW_ID
JOIN inserted BB ON A.PR_BL_ADDR_ID = BB.ROW_ID
JOIN inserted BS ON A.PR_SHIP_ADDR_ID = BS.ROW_ID

this doesn't work. I can only monitor update to one kind of addr at a time\, for instance

SELECT NAME,BM.ADDR,BB.ADDR,BS.ADDR
FROM S_ORG_EXT A
JOIN inserted BM ON A.PR_ADDR_ID = BM.ROW_ID
JOIN S_ADDR_ORG BB ON A.PR_BL_ADDR_ID = BB.ROW_ID
JOIN S_ADDR_ORG BS ON A.PR_SHIP_ADDR_ID = BS.ROW_ID

works fine. But i need to be able to monitor updates on all kinds of addresses.

Any ideas

thanks
Kuntal

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000