SQL Multiple Joins

Question

Given the following two tables:

CREATE TABLE [dbo].[MTCorrelations]
(
    [CorrelationID] [int] IDENTITY(1,1) NOT NULL,
    [StockA] [nvarchar](5) NOT NULL,
    [StockB] [nvarchar](5) NOT NULL,
    [Correlation] [float] NOT NULL,
    [LengthStr] [nvarchar](5) NOT NULL,
    [Date] [datetime] NOT NULL
)

CREATE TABLE [dbo].[Industries]
(
    [IndustryID] [int] IDENTITY(1,1) NOT NULL,
    [Symbol] [nvarchar](5) NOT NULL,
    [Sector] [nvarchar](50) NULL,
    [Industry] [nvarchar](50) NULL
)

I am trying to look up the industries of StockA and StockB from the Industries table. However I don't know how to do multiple joins. This is the best I can come up with:

SELECT TOP 1000 
[CorrelationID]

      ,[StockA]
      ,[StockB]
      ,[Correlation]
      ,b.Industry
      ,c.Industry
  FROM [MarketTopology].[dbo].[MTCorrelations] as a JOIN [MarketTopology].[dbo].[Industries] as b ON a.StockA = b.Symbol
  AND a JOIN [MarketTopology].[dbo].[Industries] as c ON a.StockB = c.Symbol

I get error on the AND. What's the correct way of doing this?

Answer 1

SELECT  TOP 1000 
        [CorrelationID]
       ,[StockA]
       ,[StockB]
       ,[Correlation]
       ,b.Industry
       ,c.Industry
FROM   [MarketTopology].[dbo].[MTCorrelations] AS a
JOIN   [MarketTopology].[dbo].[Industries] AS b
ON     b.Symbol = a.StockA
JOIN   [MarketTopology].[dbo].[Industries] AS c
ON     c.Symbol = a.StockB