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
 General SQL Server Forums
 New to SQL Server Programming
 Inner Join Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 09/30/2013 :  11:21:35  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

276 Posts

Posted - 09/30/2013 :  12:41:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/30/2013 :  12:43:02  Show Profile  Reply with Quote
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
  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.05 seconds. Powered By: Snitz Forums 2000