CODEWITHSUNDEEP
sqlsql-server
Behrooz Karjoo
Behrooz Karjoo

Reputation: 4292

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?

Upvotes: 5

Views: 3857

Answers (6)

Chandu
Chandu

Reputation: 82903

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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

Upvotes: 0

Raj More
Raj More

Reputation: 48016

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.

Upvotes: 2

Borja
Borja

Reputation: 2198

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

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 3

Neil Knight
Neil Knight

Reputation: 48547

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

Upvotes: 2

Related Questions