How to Correct the Correlation Names on This SQL Join

How can I correct the correlation names on this sql join?

Use table aliases for each reference to PoliticalFigures instead:

SELECT 
  Countries.Name AS Country, 
  P.Name AS President, 
  VP.Name AS VicePresident
FROM
  Countries
  LEFT OUTER JOIN PoliticalFigures AS P ON Countries.President_Id = P.Id
  LEFT OUTER JOIN PoliticalFigures AS VP ON Countries.VicePresident_Id = VP.Id

Correlation Names, how to use them?

INNER JOIN dbo.Assets ON Assets.AssetTypeID = AssetTypes.AssetTypeID

here is some issue, assettypes is also a table? then why you are not join in your query.

Always give alias name and try to make sort to understand as

SELECT Distinct --to remove un-wanted duplicate rows
--I don't know, so you will add alias before each column to avoid error.
DENumber, A.AcquiredDate, ItemDescription, ItemName, LocationName, AssetTypeID 

FROM dbo.Assets A
INNER JOIN dbo.Locations L ON A.LocationId = L.LocationId 
INNER JOIN dbo.Assets A1 ON A.AssetTypeID = A1.AssetTypeID
--INNER JOIN dbo.AssetsTypes AT ON A.AssetTypeID = AT.AssetTypeID      --if assettypes you want to join
WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate()) >= 7

How to use correlation names in this query

Give different alias name

SELECT P1.NAME, 
       P1.CITY, 
       P1.STATE,
       P1.ZIP, 
       NPI.[Provider First Line Business Practice Location Address] as "ADDRESS", 
       PostalCodes.County                                      
FROM Taxonomy
     INNER JOIN NPI_Taxonomy 
     ON Taxonomy.[Taxonomy Code] = NPI_Taxonomy.[Healthcare Provider Taxonomy Code] 
     RIGHT OUTER JOIN PROVIDERS P1 ON NPI_Taxonomy.NPI = P1.FACILITYID
     RIGHT OUTER JOIN npi ON P1.FACILITYID = NPI.NPI
     LEFT OUTER JOIN PostalCodes ON PostalCodes.Code = P1.ZIP 
WHERE     (1=1)
AND (P1.STATE = 'AL')
AND ([Taxonomy Specialization] like 'general acute care hospital')
order by P1.NAME

The correlation name 'CONVERT' is specified multiple times

@Kryesec is correct. Each subquery must have a unique alias.

You cannot do this:

select *
from (
   select col1
   from table1
) AS [CONVERT]
left outer join (
   select col1
   from table2
) AS [CONVERT] on [CONVERT].col1 = [CONVERT].col1

This has [CONVERT] defined twice. Because there is no way to know which [CONVERT].col1 we would actually want, this is invalid and produces the error you see.

You CAN use [CONVERT] multiple times as the table your are selecting FROM. To do so, though, each reference must be unique in scope. Meaning you don't have to alias [CONVERT] inside each subquery because it is unique inside each subquery (unique in scope). BUT each subquery then must have a unique alias, and that is where your error message comes from.

The following is what I think you want your code to be. Note I removed the aliases from inside the subqueries -- they are unnecessary, though equally they are not causing any problems. The primary change that should resolve your error is changing the final 2 subqueries from both using alias [CONVERT] to using the aliases [FROM_CONVERT] and [TO_CONVERT] respectively.

SELECT  dbo.[PART LIST].PART, 
        [COST ALL].[cost total], 
        [SELL ALL].[sell total], 
        [FROM_CONVERT].[FROM total], 
        [TO_CONVERT].[TO total]
FROM dbo.[PART LIST] 
LEFT OUTER JOIN (SELECT PART, SUM(AMT) AS [cost total]
     FROM  dbo.[COST ALL]
     WHERE (STREAM = N'Y') AND (USAGE = N'MUM')
     GROUP BY PART
) AS [COST ALL] ON [COST ALL].PART = dbo.[PART LIST].PART
LEFT OUTER JOIN (SELECT PART, SUM(AMT) AS [sell total]
    FROM dbo.[SELL ALL]
    WHERE (STREAM = N'FSA') AND (USAGE = N'MUM')
    GROUP BY PART
) AS [SELL ALL] ON [SELL ALL].PART = dbo.[PART LIST].PART
LEFT OUTER JOIN (SELECT [From PART], SUM(Amt) AS [FROM total]
    FROM dbo.[convert]
    GROUP BY [From PART]
) AS [FROM_CONVERT] ON [FROM_CONVERT].[From PART] = dbo.[PART LIST].PART 
LEFT OUTER JOIN (SELECT [TO PART], SUM(Amt) AS [TO total]
    FROM dbo.[convert]
    GROUP BY [TO PART]
) AS [TO_CONVERT] ON [TO_CONVERT].[TO PART] = dbo.[PART LIST].PART

As a side note -- you'll probably be happier if you do not use spaces in any of your column names or aliases or tables etc. This allows you to avoid quotes/square brackets. In your code, as one example, I would suggest AS COST_ALL on COST_ALL.PART = and select ... sum(amt) as FROM_TOTAL.

Additionally, it is (very) bad design to use any keywords for names of tables, columns, databases etc. It looks like you have a table named [CONVERT]. Perhaps you cannot change anything with your design at this point, but you should if you can, and you should remember this for any future projects.

The correlation name 'o' is specified multiple times in a FROM clause

Your FROM clause is:

FROM [Order] o,
     [OrderItem] oi INNER JOIN
     [Order] o
     ON o.[pkOrderID] = oi.[fkOrderID]

Just like the error message says, o is defined twice. Never use commas in the FROM clause. Presumably, you intend:

FROM [OrderItem] oi INNER JOIN
     [Order] o
     ON o.[pkOrderID] = oi.[fkOrderID]

Error: Use correlation names to distinguish them in INSERT INTO using SELECT

From the looks of it, you may be trying to update a column on an existing row in traffic_data_aggregated_lanes, in which case, the syntax should be an update:

UPDATE l
SET l.aggregated_speed = s.product
FROM    
    traffic_data_aggregated_lanes l
    INNER JOIN temp_aggregated_speed s
      ON l.ID = s.ID
      AND l.date = t.datetime;