BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


DECLARE @tableimages TABLE
(
	CapId INT,
	shortmodel VARCHAR(50),
	Doors INT,
	ImageId INT
)
DECLARE @tableimages2 TABLE
(
	CapId INT,
	shortmodel VARCHAR(50),
	Doors INT,
	ImageId INT
)

    INSERT INTO @tableimages (CapID, shortmodel, Doors, ImageId)
	SELECT Min(T1.CapID), T2.CVehicle_ShortModText, Cder_doors, Min(TCI.ID)
	FROM dbWebsiteLO..vwManufacturersPage T1
	LEFT JOIN PUB_CAR..CapVehicles T2
	ON T2.cvehicle_modtext = T1.Model AND T1.Manufacturer = @Manufacturer
	LEFT JOIN dbPubMatrix..tblCarImages TCI
	ON T2.cvehicle_id = TCI.CAPID AND TCI.StudioImages IS NOT NULL
	WHERE t1.[type] = 'car'
	GROUP BY T2.CVehicle_ShortModText, Cder_doors

	INSERT INTO @tableimages2 (CapID, shortmodel, Doors, ImageId)
	SELECT Min(T3.CapID), T4.CVehicle_ShortModText, Cder_doors, Min(TCI2.ID)
	FROM dbWebsiteLO..vwManufacturersPage T3
	LEFT JOIN PUB_LIGHTS..CapVehicles T4
	ON T4.cvehicle_modtext = T3.Model AND T3.Manufacturer = @Manufacturer
	LEFT JOIN dbPubMatrix..tblCommImages TCI2
	ON T4.cvehicle_id = TCI2.CAPID AND TCI2.StudioImages IS NOT NULL
	WHERE T3.[type] = 'commercial'
	GROUP BY T4.CVehicle_ShortModText, Cder_doors

SELECT @Manufacturer AS Manufacturer, MP.Model AS Model, 
       TI.shortmodel AS shortmodel, TCI.StudioImages AS studioimages, Doors  
FROM @tableimages TI
INNER JOIN dbPubMatrix..tblCarImages TCI
ON TI.ImageId = TCI.ID
INNER JOIN dbWebsiteLO..vwManufacturersPage MP
ON TI.CapId = MP.CapID

UNION ALL

SELECT @Manufacturer AS Manufacturer, MP2.Model AS Model, 
       TI2.shortmodel AS shortmodel, TCI2.StudioImages AS studioimages, Doors  
FROM @tableimages2 TI2
INNER JOIN dbPubMatrix..tblCommImages TCI2
ON TI2.ImageId = TCI2.ID
INNER JOIN dbWebsiteLO..vwManufacturersPage MP2
ON TI2.CapId = MP2.CapID



END

SELECT Min(T3.CapID), T4.CVehicle_ShortModText, Cder_doors, Min(TCI2.ID)
	FROM dbWebsiteLO..vwManufacturersPage T3
	LEFT JOIN PUB_LIGHTS..CapVehicles T4
	ON T4.cvehicle_modtext = T3.Model AND T3.Manufacturer = 'ford'
	LEFT JOIN dbPubMatrix..tblCommImages TCI2
	ON T4.cvehicle_id = TCI2.CAPID AND TCI2.StudioImages IS NOT NULL
	WHERE T3.[type] = 'commercial'
	GROUP BY T4.CVehicle_ShortModText, Cder_doors