Geek Answers Handbook

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 find the StockA and StockB industries from the Industries table. However, I do not know how to make multiple connections. This is the best I can think of:

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 an error in AND. What is the right way to do this?

+5
source share
6 answers

Remove AND aand just add the followingJOIN

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
+2
source 
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
+3
source

:

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
+2

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

, IMO A, B, C , - . , , , Ind Industries.

+2

AND a .

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
0

There is a typo in your request. 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
0
source

More articles:


All Articles