Return value based on value inside query

Transact-SQL
1

Hi all, so I'm pretty new to SQL and learning as I go, I have a simple select as below that works fine as far as it goes -

SELECT [DB1].[dbo].[SLCustomerAccount].[SLCustomerAccountID]
,[DB1].[dbo].[SLCustomerAccount].[CustomerAccountNumber]
,[DB1].[dbo].[SLCustomerAccount].[CustomerAccountName]
,[DB1].[dbo].[SLCustomerAccount].[CustomerAccountShortName]
,[DB1].[dbo].[SLCustomerAccount].[SLAssociatedOfficeTypeID]
,[DB1].[dbo].[SLCustomerAccount].[AssociatedHeadOfficeAccountID]
,[DB1].[dbo].[SLOfficeType].[Name]
FROM [DB1].[dbo].[SLCustomerAccount], [DB1].[dbo].[SLOfficeType]
where [DB1].[dbo].[SLCustomerAccount].[SLAssociatedOfficeTypeID] = [DB1].[dbo[.[SLOfficeType].[SLOfficeTypeID]

Basically the table it selects from has all the customer accounts - some accounts have an associated head office - these accounts are also held in the same table - so I can return the id number for these using the value in [DB1].[dbo].[SLCustomerAccount].[AssociatedHeadOfficeAccountID] but I want to return a new value that is the name related to that AssociatedHeadOfficeAccountID - it would come from CustomerAccountName

Could someone point a noob like me in the right direction, cheers

3

currently its returning something like....

image
image.png1092×136 4.72 KB

4

Between the FROM and the WHERE lines, add an INNER JOIN with the table that has the head office names, with an ON condition matching the AssociatedHeadOfficeAccountID between the two tables. In the list of SELECTed columns, use the name from that joined table instead of the ID.