Get data from 3 tables by Inner Join & group by clause.

Question

0.00/5 (No votes)

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:

SQL

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

tbl_VIP_WishList:

XML

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 :

XML

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

XML

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 11-Apr-13 2:45am

Mas11

Updated 11-Apr-13 3:32am

v8

Add a Solution

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.

4 solutions

Solution 1

See the below query.

SQL

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

Posted 11-Apr-13 3:13am

Dnyaneshwar Kondbale

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.

Solution 2

Please use the below query:

SQL

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.

SQL

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

Posted 11-Apr-13 3:17am

arindamrudra

Updated 11-Apr-13 3:33am

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.

Solution 3

Hello,

Here is how you can do this.

SQL

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,

Posted 11-Apr-13 3:31am

Prasad Khandekar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Shanalal Kasim · Accepted Answer · 2013-04-11T03:36:00

Solution 4

Hi,

Try below sql

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

Posted 11-Apr-13 3:36am

Shanalal Kasim

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