getting data from four tables and using COUNT()

Question

Post reply

getting data from four tables and using COUNT()

Hem.Singh

SSC Eights!

Points: 841
More actions
November 2, 2012 at 8:14 am

#270441

Hi,

I have four tables and I have to get some data and also have to use COUNT() on three fields of three tables (i.e. on one field of each 3 tables)

I am enclosing the sql script of my tables and there data here:

http://demo.yummyrepublic.com/LikesRateShare_script.rar

so please first check it. There are 4 tables named FoodRating, Likes, Shares & MenuFoodItems. I have to get Name of FoodItem from MenuFoodItems table as per Columns: FoodItemId, ItemId, FoodItemId of FoodRating, Likes, Shares tables respectively. Where RestoId = some RestaurantId.

And also to get the Total Counts of FoodItemId, ItemId, FoodItemId columns of FoodRating, Likes, Shares tables respectively. with GROUP Clause.

here is what I tried for the first attempt. it will give more clarification.:

DECLARE @RestoId int SET @RestoId=1; SELECT mfi.Id, mfi.Name, l.ItemId Likes, s.FoodItemId Shares, fr.FoodItemId Rating INTO #hpAll FROM Shares s RIGHT JOIN MenuFoodItems mfi ON mfi.Id=s.Id FULL OUTER JOIN Likes l ON mfi.Id=l.Id FULL OUTER JOIN FoodRating fr ON mfi.Id=fr.Id WHERE mfi.RestoId=@RestoId AND (s.Id IS NOT NULL OR l.Id IS NOT NULL OR fr.Id IS NOT NULL) --GROUP BY Likes ORDER BY mfi.Id --Create Table as Select (CTAS) SELECT Rating FoodItemId, COUNT(Rating)Rating INTO #hpRating FROM #hpAll WHERE Rating IS NOT NULL GROUP BY Rating ORDER BY Rating DESC SELECT Likes FoodItemId, COUNT(Likes) Likes INTO #hpLikes FROM #hpAll WHERE Rating IS NOT NULL GROUP BY Likes ORDER BY Likes DESC SELECT Shares FoodItemId, COUNT(Shares) Shares INTO #hpShares FROM #hpAll WHERE Rating IS NOT NULL GROUP BY Shares ORDER BY Shares SELECT * FROM #hpAll SELECT * FROM #hpRating SELECT * FROM #hpLikes SELECT * FROM #hpShares DROP TABLE #hpRating DROP TABLE #hpLikes DROP TABLE #hpShares DROP TABLE #hpAll

table #hpAll is giving me the columns on which I will have to perform Count() to get the total Shares, Likes & Rate of each item.

And I am thinking to first insert there total cont in other tables as #hpLimes & #hpShares and then merge them to return the values.

So have some confusion How to merge them. And want some help. Also if there is another way to get it done then also welcome. I will work on it now on tomorrow morning. It is already late in the office.

Edit: Actually in the above script where I am getting all the values in #hpAll table is not correct in join relationship. because the Columns: FoodItemId, ItemId, FoodItemId of FoodRating, Likes, Shares tables respectively have the values equal to Id column of MenuFoodItems. So Actual relationship should be between them. I have first used the join between these columns as showing below but it is giving extra rows and wrong Count of likes, shares and Rating.

SELECT mfi.Id, mfi.Name, l.ItemId Likes, s.FoodItemId Shares, fr.FoodItemId Rating INTO #hpAll FROM Shares s RIGHT JOIN MenuFoodItems mfi ON mfi.Id=s.FoodItemId FULL OUTER JOIN Likes l ON mfi.Id=l.ItemId FULL OUTER JOIN FoodRating fr ON mfi.Id=fr.FoodItemId WHERE mfi.RestoId=@RestoId AND (s.Id IS NOT NULL OR l.Id IS NOT NULL OR fr.Id IS NOT NULL) --GROUP BY Likes ORDER BY mfi.Id

I have tried different joins but not getting the right result.

Thanks & Regards,
Hem Singh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

Nice job with the ddl and sample data. However, it is much preferred to just include in your post so we don't have to download a file and unzip it to open up a single file inside that contains 100 lines of t-sql. For the benefit of others I will post the contents.



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Shares](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[FoodItemId] [int] NULL,

	[UserId] [varchar](20) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[Shares] ON

INSERT [dbo].[Shares] ([Id], [FoodItemId], [UserId]) VALUES (1, 5, N'1')

INSERT [dbo].[Shares] ([Id], [FoodItemId], [UserId]) VALUES (2, 5, N'1')

INSERT [dbo].[Shares] ([Id], [FoodItemId], [UserId]) VALUES (3, 5, N'1')

INSERT [dbo].[Shares] ([Id], [FoodItemId], [UserId]) VALUES (4, 5, N'1')

INSERT [dbo].[Shares] ([Id], [FoodItemId], [UserId]) VALUES (5, 5, N'1')

SET IDENTITY_INSERT [dbo].[Shares] OFF

/****** Object:  Table [dbo].[MenuFoodItems]    Script Date: 11/02/2012 19:18:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[MenuFoodItems](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[RestoId] [int] NULL,

	[Name] [varchar](100) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[MenuFoodItems] ON

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (1, 1, N'COINTREAU')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (2, 1, N'CAMPARI')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (3, 1, N'AMARETTO')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (4, 1, N'BAILEYS IRISH CREAM')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (5, 1, N'SAMBUCA')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (6, 1, N'KAHULA')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (7, 1, N'TRIPLE SEC')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (8, 1, N'MARTINI ROSSO')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (9, 1, N'MARTINI BIANCA')

INSERT [dbo].[MenuFoodItems] ([Id], [RestoId], [Name]) VALUES (10, 1, N'MARTINI EXTRA DRY')

SET IDENTITY_INSERT [dbo].[MenuFoodItems] OFF

/****** Object:  Table [dbo].[Likes]    Script Date: 11/02/2012 19:18:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Likes](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[IsResto] [bit] NULL,

	[ItemId] [int] NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[Likes] ON

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (1, 0, 5)

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (2, 0, 5)

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (3, 0, 5)

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (4, 0, 4)

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (5, 0, 3)

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (6, 0, 5)

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (7, 0, 5)

INSERT [dbo].[Likes] ([Id], [IsResto], [ItemId]) VALUES (8, 0, 4)

SET IDENTITY_INSERT [dbo].[Likes] OFF

/****** Object:  Table [dbo].[FoodRating]    Script Date: 11/02/2012 19:18:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[FoodRating](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[FoodItemId] [int] NULL,

	[Rating] [tinyint] NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[FoodRating] ON

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (1, 5, 8)

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (2, 5, 7)

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (3, 5, 6)

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (4, 4, 7)

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (5, 4, 8)

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (6, 4, 6)

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (7, 3, 7)

INSERT [dbo].[FoodRating] ([Id], [FoodItemId], [Rating]) VALUES (8, 3, 5)

SET IDENTITY_INSERT [dbo].[FoodRating] OFF

So here is where the challenge begins. You posted a query that you said isn't what you want as results. We of course can't run it because there is a variable that is undefined. Even if we could run it, the results are not what you want so it doesn't really do any good.

What we need to know is what you DO want as results. Can you either explain more clearly what you want or give us exact detailed output based on a given value for @RestoID?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Hem.Singh SSC Eights! Points: 841 More actions · Answer 2

sorry for not giving that variable. it can be any RestoId from MenuFoodItems table:



DECLARE @RestoId int

SET @RestoId=1;

and the result will be used in the link: http://yummyrepublic.com/test/restaurant.php?rId=6

and the image for this link where the records will come is:

Edit: Actually in the above script [in my previous post] where I am getting all the values in #hpAll table is not correct in join relationship. because the Columns: FoodItemId, ItemId, FoodItemId of FoodRating, Likes, Shares tables respectively have the values equal to Id column of MenuFoodItems. So Actual relationship should be between them. I have first used the join between these columns as showing below but it is giving extra rows and wrong Count of likes, shares and Rating.



SELECT mfi.Id, mfi.Name, l.ItemId Likes, s.FoodItemId Shares, fr.FoodItemId Rating

INTO #hpAll

	FROM Shares s

RIGHT JOIN MenuFoodItems mfi

	ON mfi.Id=s.FoodItemId

FULL OUTER JOIN Likes l

ON mfi.Id=l.ItemId

FULL OUTER JOIN FoodRating fr

ON mfi.Id=fr.FoodItemId

WHERE mfi.RestoId=@RestoId AND (s.Id IS NOT NULL OR l.Id IS NOT NULL OR fr.Id IS NOT NULL)

--GROUP BY Likes

ORDER BY mfi.Id

I have tried different join but not getting the right result

Thanks & Regards,
Hem Singh

Hem.Singh SSC Eights! Points: 841 More actions · Answer 3

ok if I have the result of 3 temp tables like this:



-------------------------------------------

Table #hpRating:

-------------------------------------------

FoodItemId  Name                              Rating

----------- ------------------------------ -----------

3               AMARETTO                       2

4               BAILEYS IRISH CREAM            3

5               SAMBUCA                        3

-------------------------------------------

Table @hpLike

-------------------------------------------

FoodItemId  Name                           Likes

----------- ------------------------------ -----------

3               AMARETTO                     1

4               BAILEYS IRISH CREAM          2

5               SAMBUCA                      5

-------------------------------------------

Table #hpShares

-------------------------------------------

FoodItemId  Name                           Shares

----------- ------------------------------ -----------

5               SAMBUCA                        5

then how I can merge all the above 3 tables to get a table like below:



-------------------------------------------

All Merged Tables:

-------------------------------------------

FoodItemId    Name                           Likes       Rating      Shares

------------- ------------------------------ ----------- ----------- -----------

3                 AMARETTO                       1           2           0

4                 BAILEYS IRISH CREAM             2           3           0

5                 SAMBUCA                         5           3           5

I really want to know experienced developers View. My 4 tables code & data is in @sean Lange's Post. And I want just the above Merged data in one table. Here I did it with the help of 4th table MenuFoodItems. Currently it is OK. but don't know from the performance view how much it is perfect.

So here is the Code I have written (Someone will not abuse me if visited for help in future):



DECLARE @RestoId int

SET @RestoId=1;

-- get the  Count of ItemId(FoodItemsId) from Likes table in temp table

DECLARE @hpLike TABLE(

	FoodItemId int NULL,

	Name nvarchar(100),

	Likes int NULL

);

INSERT INTO @hpLike(FoodItemId, Name, Likes)

	SELECT l.ItemId FoodItemId, mfi.Name, COUNT(l.ItemId) Likes

	FROM Likes l

	INNER JOIN MenuFoodItems mfi

	ON l.ItemId=mfi.Id GROUP BY l.ItemId, mfi.Name --ORDER BY Likes DESC

-- get the  Count of FoodItemsId from Rating table in temp table

SELECT fr.FoodItemId, mfi.Name, COUNT(FoodItemId)Rating

	INTO #hpRating

	FROM FoodRating fr

	INNER JOIN MenuFoodItems mfi

	ON fr.FoodItemId=mfi.Id	WHERE mfi.RestoId=@RestoId

	GROUP BY fr.FoodItemId, mfi.Name

-- get the  Count of FoodItemsId from Shares table in temp table

SELECT s.FoodItemId,mfi.Name, COUNT(s.FoodItemId)Shares

	INTO #hpShares	FROM Shares s

	INNER JOIN MenuFoodItems mfi

	ON s.FoodItemId=mfi.Id	WHERE mfi.RestoId=@RestoId

	GROUP BY s.FoodItemId,mfi.Name

-- Merge all the above 3 tables to get the Counts in one table

SELECT TOP 10 mfi.Id, mfi.Name, ISNULL(l.Likes,0)Likes, ISNULL(r.Rating,0)Rating, ISNULL(s.Shares,0)Shares --, l.Name lName, r.Name rName, s.Name sName --, l.FoodItemId lId, r.FoodItemId rId, s.FoodItemId sId,

	INTO #hpAll FROM MenuFoodItems mfi

		FULL OUTER JOIN @hpLike l ON mfi.Id=l.FoodItemId

		FULL OUTER JOIN #hpRating r	ON mfi.Id=r.FoodItemId

		FULL OUTER JOIN #hpShares s ON mfi.Id=s.FoodItemId

	WHERE ((l.Likes IS NOT NULL) OR (r.Rating IS NOT NULL) OR (s.Shares IS NOT NULL)) AND (l.Likes != 0)

	ORDER BY l.Likes DESC

print '-------------------------------------------'

print 'All Merged Tables:'

print '-------------------------------------------'

SELECT * FROM #hpAll

print '-------------------------------------------'

print 'Table #hpRating:'

print '-------------------------------------------'

SELECT * FROM #hpRating

print '-------------------------------------------'

print 'Table @hpLike'

print '-------------------------------------------'

SELECT * FROM @hpLike

print '-------------------------------------------'

print 'Table #hpShares'

print '-------------------------------------------'

SELECT * FROM #hpShares

DROP TABLE #hpRating

DROP TABLE #hpShares

DROP TABLE #hpAll

But I am not satisfied with it because I have used 3 temp tables to get it done. If there is another way to achieve it then most welcome.

Thanks & Regards,
Hem Singh