July 20, 2010 at 3:21 pm
As Joe was getting at, there are some significant design issues. Ideally, a redesign would be in order. However, I realize there are many times that such things are out of our control and we have to deal with what we have. Here is a way you can get at the data you need and clean up your query a little bit in the process.
SELECT COALESCE(t.Tele, 0) as Tele,
COALESCE(r.Radio, 0) as Radio,
COALESCE(p.Press, 0) as Press,
COALESCE(t.Description, r.Description, p.Description) as Description,
COALESCE(t.Category, r.Category, p.Category) as Category
FROM
(SELECT COUNT(*) AS Tele,
b.Description,
pc.Description AS Category
FROM dbo.tbl_Television t
JOIN dbo.tbl_Brand b
ON t.FK_ProductId = b.FK_ProductId
AND t.FK_BrandId = b.BrandId
JOIN dbo.tbl_Product p
ON t.FK_ProductId = p.ProductId
JOIN dbo.tbl_ProductCategories pc
ON p.FK_CategoryId = pc.CategoryId
WHERE t.AdDate BETWEEN @BeginDate AND @EndDate
AND p.ProductId = @ProductId
AND pc.CategoryId = @CategoryId
GROUP BY b.Description, pc.Description) t
FULL OUTER JOIN
(SELECT 0 COUNT(*) AS Radio,
b.Description,
pc.Description AS Category
FROM dbo.tbl_Radio r
JOIN dbo.tbl_Brand b
ON r.FK_ProductId = b.FK_ProductId
AND r.FK_BrandId = b.BrandId
JOIN dbo.tbl_Product p
ON r.FK_ProductId = p.ProductId
JOIN dbo.tbl_ProductCategories pc
ON p.FK_CategoryId = pc.CategoryId
WHERE r.AdDate BETWEEN @BeginDate AND @EndDate
AND p.ProductId = @ProductId
AND pc.CategoryId = @CategoryId
GROUP BY b.Description, pc.Description) r
ON r.Description = t.Description
AND r.Category = t.Category
FULL OUTER JOIN
(SELECT COUNT(*) AS Press,
b.Description,
pc.Description AS Category
FROM dbo.tbl_PressAD pr
JOIN dbo.tbl_Brand b
ON pr.FK_ProductId = b.FK_ProductId
AND pr.FK_BrandId = b.BrandId
JOIN dbo.tbl_Product p
ON pr.FK_ProductId = p.ProductId
JOIN dbo.tbl_ProductCategories pc
ON p.FK_CategoryId = pc.CategoryId
WHERE pr.IssueDate BETWEEN @BeginDate AND @EndDate
AND p.ProductId = @ProductId
AND pc.CategoryId = @CategoryId
GROUP BY b.Description, pc.Description) p
ON (p.Description = t.Description
AND p.Category = t.Category)
OR (p.Description = r.Description
AND p.Category = r.Category)
*edit: neglected to alias one table
July 20, 2010 at 4:48 pm
Hi Nevyn, pls don't blame yourself, we learn better everyday, if you blame yourself, what should i say of myself.
Take care.
Timotech
July 20, 2010 at 5:39 pm
Hi bteraberry, after a few editing, it worked nicely, i'm trying some other experiment on it, if anything bumps up, i'll let you know.
Thanks so much.
Timotech.
July 21, 2010 at 2:04 am
Hi All, Thanks all for your contributions, after trying some other experiments, i want to conclude that COALESCE does wonders.
Thanks all
Timotech
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply