如何连接两个具有两个不同列的表

I would like to query column product_id from table A and column ids from table B. Column ids contains several product ids for different products. For example, a cell in ids could be 'productA_id:21,productB_id:22 productC_id:43'. And in this long string different product ids are separated by ',' or ' '. In my example, product A and B is separated by ',' and product B and product C is separated by ' '.

我想查询表A中的列product_id和表B中的列ids。列ids包含针对不同产品的多个产品ID。例如,id中的单元格可以是'productA_id:21,productB_id:22 productC_id:43'。在这个长字符串中,不同的产品ID由','或''分隔。在我的例子中,产品A和B由','分开,产品B和产品C由''分开。

What I want to do is to join these two tables on product_id in table A equals productA_id in the ids column of table B.

我想要做的是在表A中的product_id上连接这两个表等于表B的ids列中的productA_id。

for example table A:

例如表A:

table B:

after inner join the two tables the result should be:

在内连接两个表之后,结果应该是:

How can I get the result by a sql query?

如何通过SQL查询获得结果?

**EDIT:**For now I can only query the tables which means that I can't normalize anything. I have to accept the tables as what they are for now. Previously I am using this query to get the content only in table B:

**编辑:**现在我只能查询表格,这意味着我无法规范化任何事情。我必须接受表格,因为它们现在是什么。以前我使用此查询只获取表B中的内容:

select * from tableB tt where exists 
(select 1 from (select distinct '%productA_id:'||product_id||' %' value from tableA) all_likes where tt.ids like all_likes.value) 
or exists 
(select 1 from (select distinct '%productA_id:'||product_id||',%' value from tableA) all_likes where tt.ids like all_likes.value) 
or exists
(select 1 from (select distinct '%productA_id:'||product_id value from tableA) all_likes where tt.ids like all_likes.value) 

But, now, I want to get the info from table A as well.

但是,现在,我想从表A中获取信息。

1 个解决方案

#1

SELECT  a.prduct_id , a.value , b.id, b.ids, b.b_value 

FROM tableA a 

INNER JOIN tableB b ON a.prduct_id = b.id

but that is very simple. are you sure that is all ?

但这很简单。你确定这就是全部吗?