# Issue with Table-Table foreign key join

**URL:** <https://forum.confluent.io/t/issue-with-table-table-foreign-key-join/4751>\
**Category:** ksqlDB\
**Created:** [12 April 2022 15:52 UTC](https://forum.confluent.io/t/issue-with-table-table-foreign-key-join/4751 "2022-04-12T15:52:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jnorcross](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/jnorcross/32/1800_2.png) [@jnorcross](https://forum.confluent.io/u/jnorcross)\
**Post date:** [12 April 2022 15:52 UTC](https://forum.confluent.io/t/issue-with-table-table-foreign-key-join/4751/1 "2022-04-12T15:52:24Z")

</div>

I have been following the example here for foreign key joins:

> **[ksqlDB 0.19 Adds Data Modeling with Foreign-Key Joins](https://www.confluent.io/blog/ksqldb-0-19-adds-data-modeling-foreign-key-joins/)**
>
> Due to popular request, ksqlDB now has data modeling capabilities—foreign-key joins between tables without co-partitioning requirements! Learn how it works, and what else is new in ksqlDB 0.19.

However, I want to expand it to a join by a struct. When I update the logic to use a struct to perform the join, the sample code works fine as shown below:

```auto
CREATE TABLE ordersstruct (
     id INT PRIMARY KEY,
     user_id Struct<u_id int, customer_id int>,
     value INT
   ) WITH (
     KAFKA_TOPIC = 'my-orders-topic', 
     VALUE_FORMAT = 'AVRO',
     PARTITIONS = 2
   );

CREATE TABLE usersstruct (
     u_id Struct<u_id INT, customer_id int> PRIMARY KEY,
     name VARCHAR,
     last_name VARCHAR
   ) WITH (
     KAFKA_TOPIC = 'my-users-topic', 
     VALUE_FORMAT = 'AVRO',
	 KEY_FORMAT = 'AVRO',
     PARTITIONS = 3
   );

CREATE TABLE orders_with_users_struct AS
SELECT * FROM ordersstruct JOIN usersstruct ON user_id = u_id
EMIT CHANGES;

INSERT INTO ordersstruct (id, user_id, value) VALUES (1, Struct(u_id := 1, customer_id := 1), 100);
INSERT INTO ordersstruct (id, user_id, value) VALUES (2, Struct(u_id := 1, customer_id := 1), 200);
INSERT INTO ordersstruct (id, user_id, value) VALUES (3, Struct(u_id := 1, customer_id := 3), 300);

INSERT INTO usersstruct (u_id, name, last_name) VALUES (Struct(u_id := 1, customer_id := 1), 'John', 'Smith');
INSERT INTO usersstruct (u_id, name, last_name) VALUES (Struct(u_id := 1, customer_id := 2), 'John2', 'Smith2');
INSERT INTO usersstruct (u_id, name, last_name) VALUES (Struct(customer_id := 3, u_id := 1), 'John3', 'Smith3');

```

However, when I try to use a similar setup using our topics, then I get weird results. Our topics are generated by Debezium which I then stream to create the appropriate structure and then compute tables from the streams. A sample of the code is shown below:

```auto
CREATE STREAM careerplan_stream 
 WITH (KAFKA_TOPIC='careerplan', KEY_FORMAT='AVRO', VALUE_FORMAT='AVRO', PARTITIONS=4);

create stream careerplan_wrap_foreignkey_stream as
SELECT Rowkey, STRUCT(personpk := cp.after->personfk, tenantkey := cp.rowkey->tenantkey) as personfk, cp.after->id
FROM careerplan_stream cp;

Create Table CAREERPLAN_TABLE
with (kafka_topic='CAREERPLAN_WRAP_FOREIGNKEY_STREAM', KEY_FORMAT='AVRO', VALUE_FORMAT='AVRO', PARTITIONS=4);

select * from careerplan_wrap_foreignkey_stream emit changes;
select * from CAREERPLAN_TABLE emit changes;

Create Stream Person_Stream
 WITH (KAFKA_TOPIC='person', KEY_FORMAT='AVRO', VALUE_FORMAT='AVRO', PARTITIONS=4);
 
CREATE STREAM PERSON_STREAM_SIMPLE AS
SELECT STRUCT(personpk := p.rowkey->personpk, tenantkey := p.rowkey->tenantkey), p.after->firstname, p.after->lastname
FROM PERSON_STREAM p
PARTITION BY STRUCT(personpk := p.rowkey->personpk, tenantkey := p.rowkey->tenantkey);

Create Table PERSON_TABLE
with (kafka_topic='PERSON_STREAM_SIMPLE', KEY_FORMAT='AVRO', VALUE_FORMAT='AVRO', PARTITIONS=4);

select * from PERSON_TABLE emit changes;

CREATE Table CAREERPLAN_PERSON_TABLE AS
select cp.ROWKEY, p.rowkey->personpk, p.firstname, p.lastname, cp.id as careerplanid
from CAREERPLAN_TABLE cp LEFT JOIN
	PERSON_TABLE p ON p.RowKey = cp.personfk
	emit changes;

```

When I query the data in the tables, it looks correct.

However, when I try to do any type of join like a left or inner join then I don’t get the expected results which should be every record from careerplan is mapped to a person record:

 ![image](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/2X/b/b6fcc002a61a6335cf865cd46f28368fe092ce02.png)

**Is there some mechanic of a table-table foreign key join that I’m not considering?**

There are some alternatives (listed below) that I have tried where the join works correctly. However, I’d like to understand more on why the foreign key join doesn’t work in this case with a struct.

1. Table-Table join using primary key by rekeying careerplan to have person key as primary key (won’t work if a user has more than one careerplan)
2. Ignoring multi-tenancy and unwrapping the struct to do an integer comparison rather than struct comparison (ie. personfk = personpk) [won’t work if we need to put multiple tenant’s in one topic]

There is one alternative that I have tried that didn’t work:

1. Attempting to join using struct notation like `p.RowKey = Struct(personpk := cp.after->personfk, tenantkey := cp.rowkey->tenantkey)` [I believe this is unsupported as the foreign key must be a column and can’t be an expression]

---

<div class="post-metadata">

**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\
**Post date:** [13 April 2022 18:30 UTC](https://forum.confluent.io/t/issue-with-table-table-foreign-key-join/4751/2 "2022-04-13T18:30:33Z")

</div>

As you are using AVRO, you might hit: [Foreign key join with Schema Registry enabled key format on the right-side table may miss results · Issue #8528 · confluentinc/ksql · GitHub](https://github.com/confluentinc/ksql/issues/8528)

We are actively working on a fix for it.

---

<div class="post-metadata">

**Author:** ![jnorcross](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/jnorcross/32/1800_2.png) [@jnorcross](https://forum.confluent.io/u/jnorcross)\
**Post date:** [13 April 2022 18:56 UTC](https://forum.confluent.io/t/issue-with-table-table-foreign-key-join/4751/3 "2022-04-13T18:56:41Z")

</div>

Thanks for the response! I will keep an eye on this ticket and try again once it is fixed.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [12 May 2022 15:53 UTC](https://forum.confluent.io/t/issue-with-table-table-foreign-key-join/4751/4 "2022-05-12T15:53:19Z")

</div>

This topic was automatically closed after 30 days. New replies are no longer allowed.