How to get column values in where condition in sub query? [closed]

Question

I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query.

SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as topic, COUNT(c.topic_id) as count 
FROM post_topics a 
JOIN posts b ON a.post_id = b.id 
JOIN post_topics c on a.post_id = c.post_id 
JOIN topics t on c.topic_id = t.id
--with test as (select id from topics)
--WHERE (a.topic_id::varchar) in (select id from topics_temp)
**WHERE (a.topic_id::varchar) in (SELECT STRING_AGG(id, ',') FROM topics_temp)
AND (t.id::varchar) NOT IN (SELECT STRING_AGG(id, ',') FROM topics_temp)**
 --AND  (t.id::varchar)  NOT IN (select id from topics_temp)
 and (b.date_posted > (('now'::text)::date - '6 mons'::interval))
GROUP BY t.id, c.topic_id,a.topic_id 
ORDER BY count DESC;

Result set:

Original query: I am passing topic id directly in the below query and counts is different.

SELECT t.id as topic_id, t.value as value, t.topic as topic, COUNT(c.topic_id) as count 
FROM post_topics a 
JOIN posts b ON a.post_id = b.id 
JOIN post_topics c on a.post_id = c.post_id 
JOIN topics t on c.topic_id = t.id --AND t.topic = 'cuisine'
WHERE a.topic_id = 'c108200f-e4dc-415e-9150-3f6c74b879e2' 
AND t.id != 'c108200f-e4dc-415e-9150-3f6c74b879e2' 
AND (b.date_posted > (('now'::text)::date - '6 mons'::interval)) 
     GROUP BY t.id, c.topic_id 
     ORDER BY count DESC 
     LIMIT 10;

SO in the where clause I have mentioned two conditions with IN and Not IN condition. Topics_temp is having 8110 distinct topic name where I should perform the above joins in each topic. But I dont know how to perform the above logic for each topic in query. Can anyone help me on this please. I am trying harder for last two days to crack this down.

Query Logic Original query: Table information: Posts: post_id, url, dateposted |Topics: topic_id,topic,value |Post_topic: post_id,topic_id

First it get topic id from where clause and starts the first join with post table - it selects all posts relevant to that topic id. In the second join - for the retrieved posts from previous query it goes to post_topics table to get relevant topics again. Now in the third join it calculates the count for each topic that we got from second join. The output for this topic id is 'c108200f-e4dc-415e-9150-3f6c74b879e2' shared already. And the first column in the output is associated topic id that we got my third join.

Answer 1

For me it's not clear what you want to achieve, maybe you could rephrase your aim in the question. (And maybe I could edit my answer to make the query)

As I unsterdand it, you want to aggregate some table on given topic.

To make your query a bit clearer, I suggest using Common Table Expression and make some temporary table. You can also look about partition.

Hope this can help.

Answer 2

I got the fix for for my query. Its just an another join with topics table.

SELECT a.topic_id as main_topic, t.id as topicid, t.value as value, t.topic as topic, COUNT(c.topic_id) as count
FROM post_topics a
JOIN posts b ON a.post_id = b.id
JOIN post_topics c on a.post_id = c.post_id
JOIN topics t on c.topic_id = t.id
**JOIN topics t2 on t2.id = a.topic_id and (t.id) <> (t2.id)**
WHERE (b.date_posted > (('now'::text)::date - '6 mons'::interval))
AND LOWER(b.source) = 'instagram'
GROUP BY t.id, c.topic_id,a.topic_id
ORDER BY c.topic_id,count DESC;