Alias for the results of the INNER JOIN and for both tables that are being joined at the same time

Question

USE [MasterDB]

GO
CREATE proc  dbo.ProductPropertiesInsert
AS
SET NOCOUNT ON;

INSERT INTO [MasterDB].[Main].[Property]
SELECT vp.Product_ID, vp.Property_ID
FROM ( SELECT * FROM [MasterDB].[Product].[Report] vr INNER JOIN [MasterDB].[Main].[Product] jt ON vr.vin=v.VIN )
     cross apply (values (208, jt.id, jt.IsExclusive), 
                         (209, jt.id, jt.IsNew),
                         (213, jt.id, jt.IsPremium)) 
                         vp(Property_ID, Product_ID, property)
WHERE vp.property=1
      /* optional code? */
      and not exists(select 1 
                     from [HGregoireCentral].[Main].[Property] p_in
                     where vp.id=p_in.id
                           and vp.Property_ID=p.Property_ID);
GO

I have this stored procedure, but I am wondering if the alias for jt is the alias for the inner join or the alias for [MasterDB].[Main].[Product].

I join it on vr.vin=v.VIN, and v stands for [MasterDB].[Main].[Product], but because v isn't defined and I need the inner join for the cross apply statement, I am not sure how to do it correctly. As of now, I think the statement wouldn't work, because I need an alias for the result of the inner join and the table [MasterDB].[Main].[Product].

How do you do this?

Report Table

vin isNew isExclusive isPremium 
11   1       1          0  
12   0       0          1 
13   1       0          1 

Main Table (used for getting the property id of the property table)

vin id 
11  10 
12  11 
13  12 

Property Table (I need to insert rows for each propertyId from the first table)

id propertyId 
10 1 
10 2 
11 3 
12 1 
12 3

Answer 1

I am unsure that your query is valid SQL Server code:

• the subquery in the from clause is not aliases

• jt is defined in a subquery, and should not be available in the outer query.

But basically, I think you don't need that subquery at all. You could just flatten the joins, like:

INSERT INTO [MasterDB].[Main].[Property]
SELECT vp.Product_ID, vp.Property_ID
FROM [MasterDB].[Product].[Report] vr 
INNER JOIN [MasterDB].[Main].[Product] jt ON jt.vin = vr.VIN
CROSS APPLY (VALUES 
    (208, jt.id, jt.IsExclusive), 
    (209, jt.id, jt.IsNew),
    (213, jt.id, jt.IsPremium)
) vp(Property_ID, Product_ID, property)
WHERE 
    vp.property=1
    AND NOT EXISTS (
        SELECT 1 
        FROM [HGregoireCentral].[Main].[Property] p_in
        WHERE vp.id = p_in.id AND vp.Property_ID = p_in.Property_ID
    )

Note that I also fixed the incorrect table alias p in the NOT EXISTS subquery - presumably, you meant p_in instead. Same goes for alias v in the join.