如何匹配具有不同主键长度的两个表

I'm trying to reference two primary keys from two different tables, but the problem is that they don't match due to its character length.

我试图从两个不同的表中引用两个主键,但问题是由于它的字符长度它们不匹配。

For an example , person.personid = '1234' and department.personid = '12345'.

例如,person.personid ='1234'和department.personid ='12345'。

Is there an SQL statement in which I can use to match the first 4 or 5 characters of the primary keys or is there any other methods that I can use.

是否有一个SQL语句,我可以使用它来匹配主键的前4或5个字符,或者我可以使用任何其他方法。

UPDATE : My sincere apologies. I am dealing with two tables. "dyndomrun.ddid" is with a Primary key and "domainregion.domainid" is without any primary key nor foreign key. "dyndomrun" table DDL is set to "character varying" and has 8 characters, whereas "domainregion" table DDL is also set to "character varying" but has 10 characters.

更新:真诚的道歉。我正在处理两张桌子。 “dyndomrun.ddid”使用主键,“domainregion.domainid”没有任何主键或外键。 “dyndomrun”表DDL设置为“字符变化”并具有8个字符,而“domainregion”表DDL也设置为“字符变化”但具有10个字符。

Problem : There are some fields in domainregion table that needs to be joined together with the primary key in dyndomrun table. I can't seem to do this with a simple SQL statement such as below

问题:domainregion表中有一些字段需要与dyndomrun表中的主键连接在一起。我似乎无法使用如下的简单SQL语句执行此操作

SELECT domainregion.domainid, dyndomrun.ddid 
FROM domainregion, dyndomrun 
WHERE domainregion.domainid = dyndomrun.ddid 
ORDER BY domainregion.domainid, dyndomrun.ddid;

I have tried JOINS, INNER JOINS, LIKE, none of them seems to work. The database that I am dealing with is purely SQL based stored using PostgreSQL.

我尝试过JOINS,INNER JOINS,LIKE,它们似乎都没有用。我正在处理的数据库纯粹基于SQL,使用PostgreSQL存储。

Please advise.

3 个解决方案

#1

Your question doesn't make sense. We don't map the primary key of one table to the primary key of another table.

你的问题没有意义。我们不会将一个表的主键映射到另一个表的主键。

Now we do map the foreign key of a dependent table to the primary key of the referenced table. But it that case the value of the column in both tables has to match. 12345 in DEPARTMENT.PERSONID must refer to a record 12345 in PERSON.PERSONID, because 1234 in .PERSON.PERSONID is literally a different person.

现在我们将依赖表的外键映射到引用表的主键。但在这种情况下,两个表中列的值必须匹配。 DEPARTMENT.PERSONID中的12345必须引用PERSON.PERSONID中的记录12345,因为.PERSON.PERSONID中的1234实际上是另一个人。

Perhaps you're working with a whacky data model, in which the dependent table has some smart key only part of which refers to the parent key. If that's the case, my condolences. But you need to give some specific details, and some indication of flavour of DBMS (because solutions will vary across different products).

也许你正在使用一个糟糕的数据模型,其中依赖表有一些智能键,其中只有一部分引用了父键。如果是这样的话,我表示哀悼。但是您需要提供一些具体的细节,以及DBMS风格的一些指示(因为不同产品的解决方案会有所不同)。

But your biggest problem is this: once you try to apply relational integrity on anything other than equality between columns you run the risk of matching 1234 in .PERSON.PERSONID with 1234 in DEPARTMENT.PERSONID, 12345 in DEPARTMENT.PERSONID and 123456 in DEPARTMENT.PERSONID. That's probably not what you want.

但是你最大的问题是:一旦你尝试在除列之间的相等之外的任何事情上应用关系完整性,你就有可能在.PERSON.PERSONID中将1234与DEPARTMENT.PERSONID中的1234,DEPARTMENT.PERSONID中的12345和DEPARTMENT中的123456进行匹配。 PERSONID。这可能不是你想要的。

#2

Did you mean something like

你的意思是什么?

select * 
  from t1
     , t2
 where to_char(t1.id) = substr(t2.id,1,4)

Or other way:

或其他方式:

select * 
  from t1
     , t2
 where t1.id = round(t2.id/10)

Both above solutions assumes that t1.id is one char shorter than t2.id.

上述两种解决方案均假定t1.id比t2.id短一个char。

HTH

#3

Try this...

SELECT * 
  FROM person
 INNER JOIN department ON person.personid = LEFT(department.personid,4)

#1