CODEWITHSUNDEEP
sqlsql-server
techno
techno

Reputation: 192

Get a value of column for which there may be or not be an entry present in previously joined tables

Here's the query I want to write...

SELECT top 10 U.userid, U.fullname, U.username, T.tid, T.msg, T.tpic, T.ttime, COUNT(R.tid)
FROM USER_DETAILS AS U
INNER JOIN TWEET AS T ON U.userid = T.tuid
LEFT OUTER JOIN RETWEET AS R ON T.tuid = R.tid
WHERE (T.tuid IN (SELECT f.fuid FROM FOLLOW AS F WHERE F.userid = @usrid) OR T.tuid = @usrid)
ORDER BY T.ttime DESC;

I know the query is wrong because of aggregation. All I want is the count of the tweets being retweeted. So what is the better way of doing this?

The tables are :

User_details(userid, fullname, username)

Follow(userid, fuid) where userid is following fuid

Tweet(tid, tuid)

Retweet(tid, ruid) where ruid is the user doing the retweet.

Upvotes: 0

Views: 42

Answers (1)

cf_en
cf_en

Reputation: 1661

It'd be useful if you explain how you know it is wrong and what you know is wrong with it, so I can be confident that all of your issues have been addressed. However, immediately obvious is that you don't have a group by clause, which you need for the aggregation to work.

SELECT top 10 U.userid, U.fullname, U.username, T.tid, T.msg, T.tpic, T.ttime, COUNT(R.tid)
FROM USER_DETAILS AS U
INNER JOIN TWEET AS T ON U.userid = T.tuid
LEFT OUTER JOIN RETWEET AS R ON T.tuid = R.tid
WHERE (T.tuid IN (SELECT f.fuid FROM FOLLOW AS F WHERE F.userid = @usrid) OR T.tuid = @usrid)
GROUP BY U.userid, U.fullname, U.username, T.tid, T.msg, T.tpic, T.ttime
ORDER BY T.ttime DESC;

Upvotes: 1

Related Questions