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
 Inner Join Help

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-30 : 11:21:35
Hey guys

Hope your well, i need some help and i am hoping you can provide an answer.

Aim – To find the Agent_Chain_No in the Mid_internal column and produce the account_id, which relates to the agent_chain_no in a new column
For eg
Account_ID MID_Internal Agent_Chain_No test
0013000000VO0h5AAD 878970069885 878970069885 0013000000VO0h5AAD
0013000000VO0h4PPE 878970069884 878970069885 0013000000VO0h5AAD

My query is

SELECT
SalesForce.DailyAccounts.[Account_ID],
SalesForce.DailyAccounts.MID_Internal,
Dim_Outlet.Agent_Chain_No
FROM SalesForce.DailyAccounts INNER JOIN
Dim_Outlet ON SalesForce.DailyAccounts.MID_Internal = Dim_Outlet.FDMSAccountNo

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-30 : 12:41:47
Maybe this:

select a.account_id
,a.mid_internal
,b.agent_chain_no
,c.account_id
from salesforce.dailyaccounts as a
inner join dim_outlet as b
on b.fdmsaccountno=a.mid_internal
inner join salesforce.dailyaccounts as c
on c.mid_internal=b.agent_chain_no
and c.fdmsaccountno=a.c.fdmsaccountno
and c.agent_chain_no=b.agent_chain_no

Not sure you need the lines marked in red
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 12:43:02
Is this what you are looking for?
;with cte as
(
SELECT
SalesForce.DailyAccounts.[Account_ID],
SalesForce.DailyAccounts.MID_Internal,
Dim_Outlet.Agent_Chain_No
FROM SalesForce.DailyAccounts INNER JOIN
Dim_Outlet ON SalesForce.DailyAccounts.MID_Internal = Dim_Outlet.FDMSAccountNo
)
select
a.[Account_ID],
a.MID_Internal,
a.Agent_Chain_No,
b.[Account_ID] as test
from
cte a
left join cte b on
a.Agent_Chain_No = Agent_Chain_No.MID_Internal;
Go to Top of Page
   

- Advertisement -