Join stream with table

Confluent Cloud
1

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,

2

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;