SQL Multiple Joins

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?

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

Remove the AND a and just have the next JOIN

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 
  JOIN [MarketTopology].[dbo].[Industries] AS c 
    ON a.StockB = c.Symbol

You can use:

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 
           JOIN [MarketTopology].[dbo].[Industries] as c ON a.StockB = c.Symbol

Try this

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

Also, IMO you should get away from using the A, B, C alias convention and give your tables aliases that mean something. That way, no matter what query you are looking at, Ind may always be shortform for Industries.

No need for the AND a before the second join.

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
        JOIN [MarketTopology].[dbo].[Industries] as c 
            ON a.StockB = c.Symbol

Your query has a typo. Change it to:

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
   JOIN [MarketTopology].[dbo].[Industries] as c ON a.StockB = c.Symbol