CODEWITHSUNDEEP
sql
dnc123
dnc123

Reputation: 141

SQL getting data from 2 tables

I've got a tricky (at least for me it's tricky) question, I want to arrange data by comment count. My first table is called all_comments which has these columns (more but not essential):

comment, target_id

My second table is called our_videos which has these columns (more but not essential):

id, title

I want to get the count of all comments that have target_id same as id on 2nd table and arrange that data by comment count. Here is example of what I want:

TABLE #1:

id    target_id
----------------
1        3
2        5
3        5
4        3
5        3

TABLE #2:

id   title
-----------
1    "test"
2    "another-test"
3    "testing"
5    "......"

This is basically saying that data, that is in 2nd database and have id of 3 have 3 comments, and data that have id of 5 have 2 comments, and I want to arrange that data by this comment count and get result like this:

RESULT:

id    title
----------------
3     "testing"
5     "......."
1     "test"
2     "another-test"

If I missed any important info needed for this question just ask, thanks for help, peace :)

Upvotes: 1

Views: 73

Answers (3)

shawnt00
shawnt00

Reputation: 17915

Some systems will let you write this even though sorting is not strictly supposed to happen on an column not included in the output. I don't necessarily recommend it but I might argue it's the most straightforward.

select id, title from videos
order by (select count(*) from comments where target_id = videos.id) desc, title

If you don't mind having it in the output it's a quick change:

select id, title from videos,
    (select count(*) from comments where target_id = videos.id) as comment_count
order by comment_count desc, title

SQL generally has a lot of options.

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

select videos.id, videos.title, isnull(cnt, 0) as cnt
from videos
left outer join 
    (select target_id, count(*) as cnt
    from comments
    group by target_id) as cnts
on videos.id = cnts.target_Id
order by isnull(cnt, 0) desc, videos.title

Upvotes: 0

Iłya Bursov
Iłya Bursov

Reputation: 24146

it is very simple query and you definitely have to look at any sql tutorial

naive variant will be:

select videos.id, videos.title, count(*) as comment_count
from videos
left outer join
comments
on (videos.id = comments.target_id)
group by videos.id, videos.title
order by comment_count desc

this version has some performance problems, because you have to group by name, to speed up it we usually do next thing:

select videos.id, videos.title, q.cnt as comment_count
from videos
left outer join
(
    select target_id, count(*)
    from comments
    group by target_id
) as q
on videos.id = q.target_id
order by q.cnt DESC

Upvotes: 2

Related Questions