Table Variable-Using Inner Join Select

Lets take a look at how to make two tables, that have a "Similar Column", joined together, by using a "Select" statement with an inner join...

 Declare @FamousPainters Table(  
      FP_id int PRIMARY KEY IDENTITY(1,1),  
      painterName nvarchar(25)  
 )  
   
 Declare @Paintings Table(  
      P_id int Primary Key Identity(1,1),  
      FP_id int,  
      paintingName nvarchar(35),  
      paintingYear nvarchar(15)  
 )  
   
 INSERT into @FamousPainters(painterName)    
 Values('Lenardo da Vinci')  
 INSERT into @FamousPainters(painterName)    
 Values('Michelagelo Buonarroti')  
 INSERT into @FamousPainters(painterName)    
 Values('Vincent van Gogh')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(1,'Portrait of Ginevra deBenci','1478-1480')  
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(1,'Mona Lisa','1503-1506')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Creation of Adam','Unknown')   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Libyan Sibyl','Unknown')  
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Delphic Sibyl','Unknown')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(3,'Starry Night','1889')  
   
 SELECT painterName, paintingName, paintingYear  
 FROM @FamousPainters  
   Inner Join @Paintings  
     On [@FamousPainters].FP_id=[@Paintings].FP_id  
 Order By [@FamousPainters].painterName  
   
 GO  

First I created two "Table Variables" one a list of famous painters, with just the names of the painters and an id. The id of the famous painters will be the similar column of both tables, it can really be anything you want, but I chose this because it was less writing, I could of chose the painters name as the "Similar Column". The second "Table Variable" is a list of all the paintings with a primary key, a foreign key, that will be the key that relates to the painter of the painting, the painting name, and the year the painting was painted.

I then filled the tables with data, the first with three painters, and the other table with paintings relating to the three painters. After I ran a "SELECT" statement to pull the columns "painterName, paintingName, paintingYear" from both tables. I then have to specify my main table, and then the table i will join to it. After i have to tell it "ON" as in when the column "FP_id" of my "@FamousPainters" table equals my "@Paintings" "FP_id" column they will "Share" the data in both columns. I finish up by ordering it by the painters name.

CA.