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
 Left join help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/16/2013 :  06:32:04  Show Profile  Reply with Quote
Hey Guys

Sorry to post so much to today but a little stuck with this query.
Aim – To create a new column called “Parentid” and populate that column with a “parentid_id”
Every Fdmsaccountno has a unique “Account_ID”. An fdmsaccountno can be found in “parentsfid” column. i want to return the account_id for parentfid

Table called #mid

Columns within #mid

FDMSAccountNo
External_ID
Parentsfid
DBA Name
Legal Name
Street
MM3-DBA-ADDR2
MM3-DBA-ADDR4
City
County
Postalcode
Country
Phone
Open_Date
Cancel_Date
First_Post_Date
Last_Post_Date
Account_ID
Edfmdaccountno
Eexternalno


bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 08/16/2013 :  06:38:18  Show Profile  Reply with Quote
-- Is this you want?
SELECT m1.fdmsaccountno, m2.Parentsfid, m2.account_ID
FROM #mid m1
LEFT JOIN #mid m2
ON m1.fdmsaccountno = m2.Parentsfid

--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/16/2013 :  11:41:34  Show Profile  Reply with Quote
HI Bandi

Thank you for your post

The final outcome was

SELECT top 1000
e1.[FDMSAccountNo],
e1.External_ID ,
e1.Parentsfid,
e1.[DBA Name],
e1.[Legal Name],
e1.Street,
e1.[MM3-DBA-ADDR2],
e1.[MM3-DBA-ADDR4],
e1.City,
e1.County,
e1.Postalcode,
e1.Country,
e1.Phone,
e1.Open_Date,
e1.Cancel_Date,
e1.First_Post_Date,
e1.Last_Post_Date,
e2.Account_ID,
e2.account_ID as ParentID
FROM #EmaxAddress e1
inner join #EmaxAddress e on e1.FDMSAccountNo = e.FDMSAccountNo
LEFT JOIN #EmaxAddress e2 ON e1.Parentsfid = e2.FDMSAccountNo
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 08/20/2013 :  00:18:33  Show Profile  Reply with Quote
quote:
Originally posted by masond

HI Bandi

Thank you for your post


Welcome

--
Chandu
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.08 seconds. Powered By: Snitz Forums 2000