July 12, 2010 at 8:57 am
Hi guys thanks for all these days, i have this query that i want to use to count records from two tables based on a criteria, but i want it to display like this:
Tele Radio Brand Category
21C/COLA,FANTA & SPRITEDRINKS
52CRUSH DRINKS
67G/SPOT ORANGE & FRUITYDRINKS
1211GOLD SPOT FROOTYDRINKS
56187 UP DRINKS
1525NEW GOLD SPOT ORANGEDRINKS
13427LACASERA DRINKS
23268FAYROUZ DRINKS
34647PEPSI DRINKS
345999COCA-COLA DRINKS
4561010SPRITE DRINKS
12341537GOLDSPOT APPLE DRINKS
but its bring this result:
Tele Radio Brand Category
01C/COLA,FANTA & SPRITEDRINKS
02CRUSH DRINKS
07G/SPOT ORANGE & FRUITYDRINKS
011GOLD SPOT FROOTYDRINKS
0187 UP DRINKS
025NEW GOLD SPOT ORANGEDRINKS
027LACASERA DRINKS
0268FAYROUZ DRINKS
0647PEPSI DRINKS
0999COCA-COLA DRINKS
01010SPRITE DRINKS
01537GOLDSPOT APPLE DRINKS
10SCHWEPPES PINEAPPLEDRINKS
207 UP DRINKS
20VIRGIN COLOUR DRINKS
40MOUNTAIN DEW DRINKS
90LIMCA DRINKS
200FANTA ORANGE DRINKS
320PEPSI,7UP & MIRINDA ETC.DRINKS
360SAVANNAH DRINK DRINKS
2070LACASERA DRINKS
2100FAYROUZ DRINKS
5520PEPSI DRINKS
6990SPRITE DRINKS
9520COCA-COLA DRINKS
this is the sql query:
SELECT COUNT(*) AS Tele, 0 AS Radio, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description
UNION
SELECT 0 AS Tele, COUNT(*) AS Radio, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description
thanks, any help is appreciated
Timotech
July 12, 2010 at 9:47 am
It looks like you may be misunderstanding what a union does. It will give you all the rows from both queries, not merge them together.
What you actually want is to do JOIN the two result sets
This is quickly thrown together and not tested, but maybe:
SELECT Telesummary.Tele, Radiosummary.Radio, Telesummary.Description, Telesummary.Category from
(COUNT(*) AS Tele, tbl_Brand.Description as Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television
INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId
INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId
INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate)
AND (tbl_Product.ProductId = @ProductId)
AND (tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Telesummary
INNER JOIN
(SELECT COUNT(*) AS Radio, tbl_Brand.Description as Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio
INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId
INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId
INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate)
AND (tbl_Product.ProductId = @ProductId)
AND (tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Radiosummary
ON Telesummary.Description = Radiosummary.Description AND
Telesummary.Category = Radiosummary.Category
There may be a more efficient way to do the whole operation, but that should at least give you the results you're expecting.
July 12, 2010 at 10:08 am
Hi Nev, thanks for your reply,
The code works wonderfully, the only remaining thing is that there are some Tele records and some radio records that are not displayed because of the inner join comparison that must match, how can i do that?
Thanks for your reply.
Timotech
July 12, 2010 at 11:21 am
My bad. I assumed from your sample data that they would have all matching rows.
You can use a full outer join instead of an inner join to bring in those rows. The downside of that is that it will give you NULL as the count value of the table that didnt have that row, but there should be a way around that. Try:
SELECT Tele = CASE Telesummary.Tele WHEN Telesummary.Tele THEN Telesummary.Tele ELSE 0, Radio = CASE Radiosummary.Radio WHEN Radiosummary.Radio THEN Radiosummary.Radio ELSE 0, Telesummary.Description, Telesummary.Category from
(COUNT(*) AS Tele, tbl_Brand.Description as Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television
INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId
INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId
INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate)
AND (tbl_Product.ProductId = @ProductId)
AND (tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Telesummary
FULL OUTER JOIN
(SELECT COUNT(*) AS Radio, tbl_Brand.Description as Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio
INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId
INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId
INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate)
AND (tbl_Product.ProductId = @ProductId)
AND (tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Radiosummary
ON Telesummary.Description = Radiosummary.Description AND
Telesummary.Category = Radiosummary.Category
Again, not tested, but I think that'll do it.
July 13, 2010 at 1:45 am
Hi Nevyn, Thanks for your reply,
I tried the following code that you sent:
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
Declare @ProductId nvarchar(6)
Declare @CategoryId nvarchar(3)
Set @BeginDate = '1/5/10'
Set @EndDate = '31/5/10'
Set @ProductId = 'dri006'
Set @CategoryId = 'dri'
SELECT Tele = CASE Telesummary.Tele WHEN Telesummary.Tele THEN Telesummary.Tele ELSE 0,
Radio = CASE Radiosummary.Radio WHEN Radiosummary.Radio THEN Radiosummary.Radio ELSE 0, Telesummary.Description,
Telesummary.Category
FROM (SELECT COUNT(*) AS Tele, tbl_Brand.Description AS Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Telesummary FULL OUTER JOIN
(SELECT COUNT(*) AS Radio, tbl_Brand.Description AS Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Radiosummary ON
Telesummary.Description = Radiosummary.Description AND Telesummary.Category = Radiosummary.Category
it gave this error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.
Thanks so much, please look into it.
Thanks
Timotech
July 13, 2010 at 6:58 am
Think I just forgot to put "END" after ELSE 0 in both case statements.
July 13, 2010 at 7:38 am
Hi Nevyn, you're the man, it worked perfectly, Thanks so much
July 13, 2010 at 8:58 am
Hi nev, Thanks so much for your help, after looking closely at the result, i discovered that some of the records were having nulls as description and category like this:
2187 UPDRINKS
01C/COLA,FANTA & SPRITEDRINKS
952999COCA-COLADRINKS
02CRUSHDRINKS
210268FAYROUZDRINKS
011GOLD SPOT FROOTYDRINKS
20727LACASERADRINKS
90NULLNULL
40NULLNULL
320NULLNULL
20NULLNULL
200NULLNULL
07G/SPOT ORANGE & FRUITYDRINKS
01537GOLDSPOT APPLEDRINKS
025NEW GOLD SPOT ORANGEDRINKS
552647PEPSIDRINKS
360NULLNULL
10NULLNULL
6991010SPRITEDRINKS
what i discovered is that if you have Telesummary.Description and Category, then those columns get the precedence and are not null, but replace Telesummary with Radiosummary and the Radio columns get precedence, can you do anything about it?
Thanks so much
Timotech
July 13, 2010 at 9:35 am
Bleh, I'm making a mess of this. Now I see why they ask people on here to post compileable versions of their tables with test data.
Also had a brain fart yesterday and for some reason used a case instead of Isnull.
Anyway, the problem here is with the full outer join the table without a matching record gets nulls. I was managing that (badly) for the total, but forgot to do it for Category and Description.
SELECT Tele = ISNULL(Telesummary.Tele,0), Radio = ISNULL(Radiosummary.Radio,0), Description=Isnull(Telesummary.Description, Radiosummary.Description), Category= Isnull(Telesummary.Category,Radiosummary.category) from
(COUNT(*) AS Tele, tbl_Brand.Description as Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television
INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId
INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId
INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate)
AND (tbl_Product.ProductId = @ProductId)
AND (tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Telesummary
FULL OUTER JOIN
(SELECT COUNT(*) AS Radio, tbl_Brand.Description as Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio
INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId
INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId
INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate)
AND (tbl_Product.ProductId = @ProductId)
AND (tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Radiosummary
ON Telesummary.Description = Radiosummary.Description AND
Telesummary.Category = Radiosummary.Category
July 13, 2010 at 10:34 am
Hi Nevyn, you're more than the man, you're a genius, a guru. Thanks so much, i've finally achieved what i wanted.
Thanks it worked perfectly.
Timotech
July 13, 2010 at 10:44 am
lol, if I was a genius I'd probably have gotten it right the first time.
July 14, 2010 at 1:42 am
To me you're a genius, and i would like to have your email, incase i need to contact u personally. Thanks
Timotech timotech@yahoo.com
July 20, 2010 at 9:05 am
Hi Nevyn,
Thanks for your help, Pls i need one more help, I need to add another column to the results that i have, this is the column i want to add:
SELECT COUNT(*) AS Press, dbo.tbl_Brand.Description, dbo.tbl_ProductCategories.Description AS Category
FROM dbo.tbl_PressAD INNER JOIN
dbo.tbl_Brand ON dbo.tbl_PressAD.FK_ProductId = dbo.tbl_Brand.FK_ProductId AND dbo.tbl_PressAD.FK_BrandId = dbo.tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product ON dbo.tbl_PressAD.FK_ProductId = dbo.tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories ON dbo.tbl_Product.FK_CategoryId = dbo.tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_PressAD.IssueDate BETWEEN @BeginDate AND @EndDate) AND (dbo.tbl_Product.ProductId = @ProductId) AND
(dbo.tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY dbo.tbl_Brand.Description, dbo.tbl_ProductCategories.Description
According to your previous answers this is the code you gave that worked:
SELECT ISNULL(Telesummary.Tele, 0) AS Tele, ISNULL(Radiosummary.Radio, 0) AS Radio, ISNULL(Telesummary.Description, Radiosummary.Description)
AS Description, ISNULL(Telesummary.Category, Radiosummary.Category) AS Category
FROM (SELECT COUNT(*) AS Tele, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Telesummary FULL OUTER JOIN
(SELECT COUNT(*) AS Radio, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Radiosummary ON
Telesummary.Description = Radiosummary.Description AND Telesummary.Category = Radiosummary.Category
I tried this but it was giving me problems:
SELECT ISNULL(Telesummary.Tele, 0) AS Tele, ISNULL(Radiosummary.Radio, 0) AS Radio, ISNULL(PressSummary.Press, 0),
ISNULL(Telesummary.Description, Radiosummary.Description) AS Description, ISNULL(Telesummary.Category, Radiosummary.Category)
AS Category
FROM (SELECT COUNT(*) AS Tele, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Telesummary FULL OUTER JOIN
(SELECT COUNT(*) AS Radio, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Radiosummary FULL OUTER JOIN
(SELECT COUNT(*) AS Press, dbo.tbl_Brand.Description, dbo.tbl_ProductCategories.Description AS Category
FROM dbo.tbl_PressAD INNER JOIN
dbo.tbl_Brand ON dbo.tbl_PressAD.FK_ProductId = dbo.tbl_Brand.FK_ProductId AND
dbo.tbl_PressAD.FK_BrandId = dbo.tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product ON dbo.tbl_PressAD.FK_ProductId = dbo.tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories ON dbo.tbl_Product.FK_CategoryId = dbo.tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_PressAD.IssueDate BETWEEN @BeginDate AND @EndDate) AND (dbo.tbl_Product.ProductId = @ProductId) AND
(dbo.tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY dbo.tbl_Brand.Description, dbo.tbl_ProductCategories.Description) AS PressSummary ON
Telesummary.Description = Radiosummary.Description AND Telesummary.Category = Radiosummary.Category AND
Radiosummary.Description = PressSummary.Description AND Radiosummary.Category = PressSummary.Category
It brought this error:
Error in ON clause near 'Category'.
Unable to parse query text.
Please what can i do?
Thanks
Timotech
July 20, 2010 at 10:51 am
Hi, Thanks for your replies, but most books that we read use these conventions that i use, any way i tried something and its working but with a little issue. This is the code:
SELECT ISNULL(Telesummary.Tele, 0) AS Tele, ISNULL(Radiosummary.Radio, 0) AS Radio, ISNULL(PressSummary.Press, 0) AS Press,
ISNULL(OutdoorSummary.Outdoor, 0) AS Outdoor, ISNULL(Telesummary.Description, Radiosummary.Description) AS Description,
ISNULL(Telesummary.Category, Radiosummary.Category) AS Category
FROM (SELECT COUNT(*) AS Tele, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Television INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Television.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Television.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Telesummary FULL OUTER JOIN
(SELECT COUNT(*) AS Radio, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Radio INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Radio.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Radio.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS Radiosummary ON
Telesummary.Description = Radiosummary.Description AND Telesummary.Category = Radiosummary.Category FULL OUTER JOIN
(SELECT COUNT(*) AS Press, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_PressAD INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_PressAD.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_PressAD.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_PressAD.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_PressAD.IssueDate BETWEEN @BeginDate AND @EndDate) AND (tbl_Product.ProductId = @ProductId) AND
(tbl_ProductCategories.CategoryId = @CategoryId)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS PressSummary ON
Radiosummary.Description = PressSummary.Description AND Radiosummary.Category = PressSummary.Category FULL OUTER JOIN
(SELECT COUNT(*) AS Outdoor, tbl_Brand.Description, tbl_ProductCategories.Description AS Category
FROM dbo.tbl_Outdoor INNER JOIN
dbo.tbl_Brand AS tbl_Brand ON dbo.tbl_Outdoor.FK_ProductId = tbl_Brand.FK_ProductId AND
dbo.tbl_Outdoor.FK_BrandId = tbl_Brand.BrandId INNER JOIN
dbo.tbl_Product AS tbl_Product ON dbo.tbl_Outdoor.FK_ProductId = tbl_Product.ProductId INNER JOIN
dbo.tbl_ProductCategories AS tbl_ProductCategories ON tbl_Product.FK_CategoryId = tbl_ProductCategories.CategoryId
WHERE (tbl_Product.ProductId = @ProductId) AND (tbl_ProductCategories.CategoryId = @CategoryId) AND
(dbo.tbl_Outdoor.DateCaptured BETWEEN @BeginDate AND @EndDate)
GROUP BY tbl_Brand.Description, tbl_ProductCategories.Description) AS OutdoorSummary ON
PressSummary.Description = OutdoorSummary.Description AND PressSummary.Category = OutdoorSummary.Category
It came out with these results:
Insert Into #mytable (Tele Radio Press Outdoor Description Category)
SELECT '0','2','0','0','M-TEL','TELECOMMUNICATIONS' UNION ALL
SELECT '4','0','0','0','ZOOM MOBILE','TELECOMMUNICATIONS' UNION ALL
SELECT '4042','19169','63','143','ETISALAT(EMTS)','TELECOMMUNICATIONS' UNION ALL
SELECT '2978','1071','102','2588','GLOBACOM','TELECOMMUNICATIONS' UNION ALL
SELECT '9513','11864','112','3154','MTN','TELECOMMUNICATIONS' UNION ALL
SELECT '0','76,'13','47','MULTILINK','TELECOMMUNICATIONS' UNION ALL
SELECT '0', '1064','3','144','STARCOM','TELECOMMUNICATIONS' UNION ALL
SELECT '8','745','3','34','VISAFONE','TELECOMMUNICATIONS' UNION ALL
SELECT '1369','4761','20','475','ZAIN','TELECOMMUNICATIONS' UNION ALL
SELECT '0','0','0','29','NULL','NULL' UNION ALL
but the last row actually belongs to the 2nd under the column Outdoor, please if you understand what i mean, please how can i make the second row to display like this:
SELECT '4','0','0','29','ZOOM MOBILE','TELECOMMUNICATIONS'
and totally eliminate the last row.
Thanks
Timotech
July 20, 2010 at 2:01 pm
Actually, you can blame me for the isnulls and the full outer joins. I am appropriately humbled. Hadn't heard of coalesce before, Im embarassed to say, and the joins were an effort to get him his expected output based on what he asked, and there was only one of them. From here on out, I'll leave him to the experts.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply