Multiple JOINs on same field....

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

General SQL Server Forums

New to SQL Server Programming

Multiple JOINs on same field....

Author

Topic

redeyedbass
Starting Member

7 Posts

Posted - 2011-08-09 : 11:43:21

Hello all,

I have a query that has an inner join, such as this:


SELECT
  Calls.Call_ID
  ,Calls.Call_HistoFlag
  ,Calls.Call_RefCallID
  ,Calls.Call_Client_ID
  ,Calls.Call_LST_QUEUE
  ,Calls.Call_LST_CALLCAT
  ,Calls.Call_LST_CALLPRIO
  ,Lang_Text2 As QueueNameEn
  ,Lang_Text As QueueNameFr
FROM
  Calls INNER JOIN Lang ON Calls.Call_LST_QUEUE = Lang.Lang_Id

But now the problem is that I need to do a triple join (3 tables) and return Lang_Text2 and Lang_Text fitting the criterias of this triple join.

Here are the tables:

Table: Calls
Field: Call_ID

Table: C2_Call_UDF
Field: CUD_CALL_ID
Field: SomeNumericField

Table: Lang
Field: Lang_Id
Field: Lang_Text
Field: Lang_Text2

The join I need to do needs to join on Calls.Call_ID = C2_Call_UDF.CUD_CALL_ID and when a match is found, return the record from Lang whose Lang_Id = C2_Call_UDF.SomeNumericField

Any ideas ? I can do this triple join, but then it gets in the way of the first INNER JOIN...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 12:12:59

you mean this?


SELECT
  Calls.Call_ID
  ,Calls.Call_HistoFlag
  ,Calls.Call_RefCallID
  ,Calls.Call_Client_ID
  ,Calls.Call_LST_QUEUE
  ,Calls.Call_LST_CALLCAT
  ,Calls.Call_LST_CALLPRIO
  ,Lang.Lang_Text2 As QueueNameEn
  ,Lang.Lang_Text As QueueNameFr
  ,l2.Lang_Text2
  ,l2.Lang_Text 
FROM
  Calls INNER JOIN Lang ON Calls.Call_LST_QUEUE = Lang.Lang_Id
INNER JOIN C2_Call_UDF ON Calls.Call_ID = C2_Call_UDF.CUD_CALL_ID 
INNER JOIN Lang l2 ON l2.Lang_Id = C2_Call_UDF.SomeNumericField

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

redeyedbass
Starting Member

7 Posts

Posted - 2011-08-09 : 12:40:57

That actually works great. Let me try and understand the logic.

The 2nd INNER JOIN is kind of an "if" clause, and the 3rd one is what should be done if the "if" is true ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 12:46:01

nope not if clause. second join looks for matches from first part and then based on that it grabs values from 3rd join

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

redeyedbass
Starting Member

7 Posts

Posted - 2011-08-09 : 12:53:30

Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources