Click here to Skip to main content
14,331,362 members
Rate this:
Please Sign up or sign in to vote.
See more:
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:
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 :
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. 
Posted
Updated 11-Apr-13 3:32am
v8
Comments
Alexrf 11-Apr-13 8:58am
   
hi!
please write Primary and foreign keys
Mas11 11-Apr-13 9:04am
   
plz see, updated info.
arindamrudra 11-Apr-13 9:06am
   
In the tbl_CommonWish the commonwishid contains duplicate. Is it correct value?
Mas11 11-Apr-13 9:09am
   
No, I have updated, plz chk.
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   
Comments
Mas11 11-Apr-13 9:17am
   
Thanks for the answer, but it doesn't provide a solution.
arindamrudra 11-Apr-13 9:19am
   
It is not returning the Randy Orton I want to join french class 2 john result
Mas11 11-Apr-13 9:27am
   
Plz see in solution 2 for the detail.
Mas11 11-Apr-13 9:33am
   
Apologize ! Plz see updated question.
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
   
v2
Comments
Mas11 11-Apr-13 9:26am
   
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)
arindamrudra 11-Apr-13 9:36am
   
If you need the result like :
John Cena I want to purchase mobile 6 mas
CM Punk I'm looking for skin doctor 3 sam
Randy Orton I want to join french class 3 john

Please checke my updated answer.
arindamrudra 11-Apr-13 9:31am
   
Then how you can write John Cena I want to purchase mobile 3 mas, can you please clarify
Mas11 11-Apr-13 9:34am
   
Really Apologize ! Plz see updated question.
arindamrudra 11-Apr-13 9:38am
   
Please check my updated answer.
Rate this:
Please Sign up or sign in to vote.

Solution 3

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,
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

Hi,

Try below sql

select r.firstname + ' ' + r.lastname ,v.wishname,
(select count(*) from tbl_CommonWish where tbl_CommonWish.WishID= v.WishID ) as TotalWishedBy, r.userid
from [dbo].[tbl_registration] r  ,tbl_VIP_WishList v
where r.userid = v.userid
   
Comments
Mas11 12-Apr-13 5:13am
   
Wonderful my brother ! It works for me. I have done some minor changes in it. But finally this is the solution. I want to mark it as 100 mark but 5 is max limit. Cheers dear :)
Shanalal Kasim 12-Apr-13 5:16am
   
Thanks brother

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100