连接三个表，其中一个表没有主键 [英] Join three tables with one of them not having a primary key

问题描述

我有三个要合并在一起的表。

I have three tables that i would like to join up together.

Domain table
-domainid - Primary key

DomainRegion table
-domainid - Don't have a Primary Key or Foreign Key
-dombegin
-domend

DynDomrun table
-ddid - Primary Key
-confid1
-confid2

domain.domainid， domainregion.domainid和 dyndomrun.ddid具有相同类型的数据类型，字符不同。

"domain.domainid", "domainregion.domainid" and "dyndomrun.ddid" have the same type of data types, "character varying".

现在，问题出在该 domain.domainid和 domainregion.domainid中有两个额外的字符，我似乎无法与 dyndomrun.ddid进行联接。

Now, the problem is that "domain.domainid" and "domainregion.domainid" has two extra characters in it that i can't seem to do a join with "dyndomrun.ddid".

但是， domainid与 ddid相关，只是没有为 domainregion.domainid分配主键或外键，我需要从

However, "domainid" is related to "ddid", just that "domainregion.domainid" is not assigned a primary key or a foreign key and i need some fields from that table to join up with the others.

最后，我需要一个完整的表，其中列出了 dyndomrun.ddd，domainregion.dombegin，domainregion。 domend，dyndomrun.confid1，dyndomrun.confid2。

In the end, I need to have a single, full table listing the "dyndomrun.ddid, domainregion.dombegin, domainregion.domend, dyndomrun.confid1, dyndomrun.confid2".

我尝试使用 where（ domain.domainid，8）= left（ dyndomrun.ddid，8），它给了我一个错误，指出

I have tried using where ("domain.domainid",8)=left("dyndomrun.ddid",8), it gave me an error stating

错误：函数剩余（字符变化，整数）不存在
提示：没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型强制转换。

我尝试使用联接和其他sql语句，但它们似乎都不起作用。

I have tried using joins, and other sql statements and they all don't seem to work.

关于如何解决此问题的任何想法吗？

Any ideas as to how to solve this problem?

谢谢。

推荐答案

除了我回答的内容以外 ，您还需要在 PostgreSQL中标识符的语法。

In addition to what I answered here, you also need to read up on the syntax of identifiers in PostgreSQL.

此：

"domain.domainid"

必须是

"domain"."domainid"

或更简单的是：

domain.domainid

这篇关于连接三个表，其中一个表没有主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！