# Join stream with table

**URL:** <https://forum.confluent.io/t/join-stream-with-table/10338>\
**Category:** Confluent Cloud\
**Created:** [6 March 2024 10:55 UTC](https://forum.confluent.io/t/join-stream-with-table/10338 "2024-03-06T10:55:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![KarimizandiA](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/karimizandia/32/3658_2.png) [@KarimizandiA](https://forum.confluent.io/u/KarimizandiA)\
**Post date:** [6 March 2024 10:55 UTC](https://forum.confluent.io/t/join-stream-with-table/10338/1 "2024-03-06T10:55:29Z")

</div>

Hey every one I try to join a stream with a table.

in the table I categorised based on the id (which I call it page) and in this table right now I only have one record and in the stream I have 10 messages with the same id (page). So when I join this stream with that table base on the page which is equal for both of them I expect to get 10 item but I only get 2 record. Has anyone face with such an issue?

Best regards,

---

<div class="post-metadata">

**Author:** ![KarimizandiA](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/karimizandia/32/3658_2.png) [@KarimizandiA](https://forum.confluent.io/u/KarimizandiA)\
**Post date:** [7 March 2024 11:47 UTC](https://forum.confluent.io/t/join-stream-with-table/10338/2 "2024-03-07T11:47:48Z")

</div>

For more information I put my queries as well

create stream dev\_ct\_cms\_alg\_sample\_stream  
WITH (  
VALUE\_FORMAT=‘AVRO’,  
PARTITIONS = 2  
) as  
SELECT  
m.entityId AS page,  
tags,  
explode(json\_keys(extractjsonfield(m.fields, ‘$.sectionBody’))) AS locale,  
json\_records(extractjsonfield(m.fields, ‘$.sectionBody’)) as records,  
case  
when eventName = ‘ContentManagement.Entry.publish’ then eventDate  
else publishedDate  
end as publishedDate  
from dev\_ct\_cms\_producer\_stream m  
where contentType = ‘page’  
emit changes;

CREATE TABLE dev\_ct\_cms\_alg\_sample\_table  
WITH (  
VALUE\_FORMAT=‘AVRO’,  
PARTITIONS = 2  
) AS  
select page,  
TOPKDISTINCT(publisheddate, 2) as publisheddates  
from dev\_ct\_cms\_alg\_sample\_stream  
where publishedDate is not null  
group BY page  
emit changes;

select s.\*  
from dev\_ct\_cms\_alg\_sample\_stream s left join dev\_ct\_cms\_alg\_sample\_table t on s.page = t.page  
where  
(s.publisheddate = t.publishedDates[1] or s.publisheddate = t.publishedDates[2]) and  
s.page = ‘4PTAtQfSBgr5vMU2ZnFHjj’  
emit changes;