Article: Multiple Joins to the same Table in a Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Multiple Joins to the same Table in a Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-14 : 01:01:53

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 Post

Posted - 2002-05-13 : 15:03:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources