通过Inner Join& amp;获得3张桌子的数据。 group by子句。 [英] Get data from 3 tables by Inner Join & group by clause.

问题描述

我有3个表（ tbl_registration，tbl_VIP_WishList，tbl_CommonWish ），其中有列：



tbl_registration> FirstName，LastName，UserID

tbl_VIP_WishList> WishID，产品，愿望，用户ID

tbl_CommonWish> CommonWishID，WishID，UserID





tbl_registration：

I have 3 tables (tbl_registration, tbl_VIP_WishList, tbl_CommonWish) in which it has column :

tbl_registration > FirstName, LastName, UserID
tbl_VIP_WishList > WishID, Product, WishName, UserID
tbl_CommonWish > CommonWishID, WishID, UserID


tbl_registration:

UserID(pk)    FirstName           LastName
 
mas         John              Cena            
sam         CM                Punk                                       
john        Randy             Orton  

tbl_VIP_WishList：

tbl_VIP_WishList:

WishID(pk)    Product           WishName                         UserID(fk-tbl1)

1         Electronics       I want to purchase mobile        mas
2         Health            I'm looking for skin doctor      sam
3         Education         I want to join french class      john  

tbl_CommonWish：

tbl_CommonWish :

CommonWishID(pk) WishID(fk-tbl2) UserID(fk)  Desc

1               1              mas       I want it.
2               2              sam       I want to buy
3               1              sam       I also need this
4               3              mas       I also need this
5               2              mas       I also need this
6               2              john      I want it
7               1              john      I need this

现在我想要这种格式的数据

Now I want data in this format

FullName        WishName               TotalWishedBy(count- from tbl3)    UserID
  
John Cena       I want to purchase mobile       3              mas   
CM Punk         I'm looking for skin doctor     3              sam
Randy Orton     I want to join french class     1             john  

Actually In Result column: "TotalWIshedBy" is the sum of the wish which is also liked by other userid (takes data from tbl_commonwish) & UserID (it is the wish written by UserID > Takes data from tbl_wishlist).   <br />
I really appreciate any suggetion. 

推荐答案

请参阅以下查询。

See the below query.

select a.FirstName +' '+a.LastName as FullName,
b.wishName, count(c.wishId) as TotalWishedBy,
a.UserId from tbl_registration a inner join tbl_VIP_WishList b on a.UserID=b.UserID
inner join tbl_CommonWish c on a.userId=c.UserID and b.WishID =c.WishID
group by
a.FirstName ,a.LastName,b.wishName,a.UserId

请关注我们e以下查询：

Please use the below query:

SELECT R.FirstName + ' ' + R.LastName AS FullName, W.WishName, A.TotalWishedBy, R.UserID FROM tbl_registration AS R
INNER JOIN tbl_VIP_WishList AS W ON R.UserID = W.UserID
INNER JOIN (SELECT ISNULL(COUNT(*),0) AS TotalWishedBy,UserID FROM tbl_CommonWish GROUP BY UserID) AS A ON A.UserID = R.UserID

希望这会对你有所帮助。



更新：



如果您正在寻找金额，请使用以下查询。

Hope this will help you.

Update:

If you are looking for sum, please use the below query.

SELECT R.FirstName + ' ' + R.LastName AS FullName, W.WishName, A.TotalWishedBy, R.UserID FROM tbl_registration AS R
INNER JOIN tbl_VIP_WishList AS W ON R.UserID = W.UserID
INNER JOIN (SELECT ISNULL(SUM(WishID),0) AS TotalWishedBy,UserID FROM tbl_CommonWish GROUP BY UserID) AS A ON A.UserID = R.UserID

你好，



以下是你如何做到这一点。

Hello,

Here is how you can do this.

SELECT tr.FirstName + ' ' + tr.LastName As FullName, 
	tw.WishName, COUNT(cw.WishID), cw.UserID
FROM tbl_CommonWish cw
	LEFT JOIN tbl_registration tr ON tr.UserID = cw.UserID
	LEFT JOIN tbl_VIP_WishList tw ON tw.WishID = cw.WishID
GROUP BY cw.UserID

问候，

Regards,

这篇关于通过Inner Join& amp;获得3张桌子的数据。 group by子句。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！