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
 Left join help

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-16 : 06:32:04
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 06:38:18
-- 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 - 2013-08-16 : 11:41:34
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 00:18:33
quote:
Originally posted by masond

HI Bandi

Thank you for your post


Welcome

--
Chandu
Go to Top of Page
   

- Advertisement -