-1

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: Result for Above query

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;

query with specific topic

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.

closed as off-topic by Gaius, Marcello Miorelli, John aka hot2use, Marco, MDCCL Aug 12 at 23:06

If this question can be reworded to fit the rules in the help center, please edit the question.

0

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.

  • I have added the query logic in my question. My original query works fine when I give one topic in where but when I supply multiple topic id its not giving the correct count. Which means the group by is not working. – prabhu Jul 31 at 9:37
0

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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.