sql select query among 3 tables

Question

I have three tables:

ITEMDISPLAYCOuNTS *this table stores who displayed which posts and howmanytimes

postid, count, whodisplayedid

POSTS *this table stores who posted what?

postid, whopostedid

ASPNET_USERS

userid, username

What I want at the end is who displayed whose post, and how many times, with usernames, not userids:

OUTPUT

UserNameWhoDisplayed, UserNameWhosePost, Count

I wrote the following code, but it is not functioning properly.

SELECT u1.UserName, u2.UserName, ItemDisplayCounts.Count
FROM ItemDisplayCounts AS i, Posts AS p, aspnet_Users AS u1, aspnet_Users AS u2
WHERE p.UserId = u2.UserId AND i.UserId = u1.UserId AND i.PostId = p.PostId

Can anyone suggest any corrections?

what does you mean by "not functioning properly"? What RDBMS do you using? — Roman Pekar, Oct 11 '13 at 20:16

score 3 · Accepted Answer · answered Oct 11 '13 at 20:15

3

I think you want something like this (changed your joins to proper ANSI ones):

select
    iu.UserName as UserNameWhoDisplayed,
    pu.UserName as UserNameWhosePost,
    sum(i.Count) as [Count]
from ItemDisplayCounts as i
    inner join aspnet_Users as iu on iu.userid = i.whodisplayedid
    inner join posts as p on p.postid = i.postid
    inner join aspnet_Users as pu on pu.userid = p.whopostedid
group by
    iu.UserName,
    pu.UserName

answered Oct 11 '13 at 20:15

Roman Pekar

107,110
28
195
197

I actually do not need sum() function. I removed group by lines, and used only i.Count in the select statement. I guess this will be the same except that it will repeat the same UserNameWhoDisplayed couple of times. Thanks! – renakre Oct 11 '13 at 20:33

Linger · Answer 2 · 2013-10-11T20:43:15.487

If I am understanding you correctly you do not need to perform any math in the query, since you have the count field in the ITEMDISPLAYCOuNTS table. So, I do believe this will work for you:

SELECT U2.username AS UserNameWhoDisplayed, 
       U1.username AS UserNameWhosePost, 
       ID.[Count]
FROM ((POSTS AS P 
       INNER JOIN ASPNET_USERS AS U1 ON P.whopostedid = U1.userid) 
       INNER JOIN ITEMDISPLAYCOuNTS AS ID ON P.postid = ID.postid) 
       INNER JOIN ASPNET_USERS AS U2 ON ID.whodisplayedid = U2.userid

sql select query among 3 tables

2 Answers2