getting data from four tables and using COUNT()

Question

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: 286590 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.IdWHERE 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 #hpSharesFROM Shares s

INNER JOIN MenuFoodItems mfi

ON s.FoodItemId=mfi.IdWHERE 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 rON 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