SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SP Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 04/21/2008 :  05:08:21  Show Profile  Reply with Quote
Hi,

I've got the SP below, for some reason i'm only getting rows in the final result set from the first part of the query (@tableimages)

If I run the subquery which generates @tableimages2 seperately it returns results but for some reason they are not going into the union.

Thanks


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

harsh_athalye
Flowing Fount of Yak Knowledge

India
5518 Posts

Posted - 04/21/2008 :  05:16:06  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
That means either your table - @tableimages2 is empty or one of the joins in the second SELECT statement is failing.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 04/21/2008 :  05:22:19  Show Profile  Reply with Quote
If I run this I get results


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


Whats the best way to troubleshoot the other bit?

Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000