Join 3 tables

Question

Post reply

Join 3 tables

sheraazb

Newbie

Points: 3
More actions
December 6, 2010 at 4:23 pm

#232154

Hi All
i have 3 tables that i need to join. My case is as such. I have a business rule that is related to a system and it is also related to a business rule source.
I need to write a query to get the business rule info together with its assigned system and source. I did try a multiple join but i guess multiple joins work best when built on top of one another but as for me i need to join the business rule with the systems table and then join the business rule with the sources table.
Any help would be appreciated.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

That's a rather vague description...

Please post table def and sample data as described in the first link in my signature together with the expected result.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sheraazb Newbie Points: 3 More actions · Answer 2

Business Rules Table

BR_ID (PK)

BR_SYS_ID (FK)

BR_SRC_ID (FK)

BR_NAME

BR_DESC

Sample Data

BR_ID | BR_SYS_ID | BR_SRC_ID | BR_NAME | BR_DESC

187 | 1 | 5 | TEST | 301

Systems Table

SYS_ID (PK)

SYS_CODE

SYS_NAME

Sample Data

SYS_ID | SYS_CODE | SYS_NAME

1 | CLAS | Consolidated life assurance system

Source Table

SRC_ID (PK)

SRC_NAME

Sample data

SRC_ID | SRC_NAME

5 | Internal

My query

SELECT

BR.BR_ID

, BR.BR_SYS_ID

, SYS.SYS_CODE

, BR.BR_SRC_ID

, SRC.SRC_NAME

, BR.BR_DESC

FROM

dbo.TBL_BUSINESS_RULES BR

LEFT JOIN dbo.TBL_SYSTEMS SYS

ON BR.BR_SYS_ID = SYS.SYS_ID

LEFT JOIN dbo.TBL_SOURCES SRC

ON BR.BR_SRC_ID = SRC.SRC_ID

WHERE

BR.BR_ID = 187--@_BR_ID

My result

187 | 1 | CLAS | 5 | NULL | 301

Result that i would like to achieve

for the SRC_NAME i would like to get the corresponding source name from the source table.

187 | 1 | CLAS | 5 | Internal | 301

sheraazb Newbie Points: 3 More actions · Answer 3

figured it out..

wasn't working because in my case there wasn't any record in the source table associated with the source id in the business rules table.

silly of me to overlook that...