将预先连接的表连接到一张表中

发布于 2024-12-22 23:41:14 字数 744 浏览 0 评论 0原文

我有两个连接表，我之前已与其他表连接，我希望“ddid”与“confid1”链接，

这是代码：

SELECT r.domainid, r.dombegin, r.domend, d.ddid 
FROM   domainregion AS r
JOIN   dyndomrun AS d ON r.domainid::varchar(8) = d.ddid 
ORDER  BY r.domainid, d.ddid, r.dombegin, r.domend;

最后

(SELECT confid1 as id1, conformer.pdbcode, conformer.chainid
from dyndomrun JOIN conformer 
           ON dyndomrun.confid1 = conformer.id)
UNION
(SELECT confid2 as id2, conformer.pdbcode, conformer.chainid 
 from dyndomrun JOIN conformer ON dyndomrun.confid2 = conformer.id)

，我想要一个包含domainid、dombegin的新表、domend、ddid、confid1、pdbcode、chainid。

这是否可以做到，因为当我尝试对预连接表进行并集时，它给我带来了一个错误，指出每个 UNION 查询必须具有相同数量的列。

请指教。谢谢。

原文

I have two joined tables that i have joined up with other tables previously and i would like "ddid" to link up with "confid1"

Here is the code :

SELECT r.domainid, r.dombegin, r.domend, d.ddid 
FROM   domainregion AS r
JOIN   dyndomrun AS d ON r.domainid::varchar(8) = d.ddid 
ORDER  BY r.domainid, d.ddid, r.dombegin, r.domend;

and

(SELECT confid1 as id1, conformer.pdbcode, conformer.chainid
from dyndomrun JOIN conformer 
           ON dyndomrun.confid1 = conformer.id)
UNION
(SELECT confid2 as id2, conformer.pdbcode, conformer.chainid 
 from dyndomrun JOIN conformer ON dyndomrun.confid2 = conformer.id)

At the end, I would like to have a new table containing domainid, dombegin, domend, ddid, confid1, pdbcode, chainid.

Is this possible to do because it threw me an error when i tried doing a union of a pre-joined table, stating that each UNION query must have the same number of columns.

Please advise. Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怎会甘心 2024-12-29 23:41:14

如果我理解正确的话：

SELECT
  r.domainid,
  r.dombegin,
  r.domend,
  d.ddid,
  d.confid1 AS confid,
  c.pdbcode,
  c.chainid
FROM dyndomrun d
  INNER JOIN conformer c ON d.confid1 = c.id
  INNER JOIN domainregion r ON r.domainid::varchar(8) = d.ddid
UNION ALL
SELECT
  NULL,
  NULL,
  NULL,
  d.ddid,
  d.confid2,
  c.pdbcode,
  c.chainid
FROM dyndomrun d
  INNER JOIN conformer c ON d.confid2 = c.id

If I understand you correctly:

SELECT
  r.domainid,
  r.dombegin,
  r.domend,
  d.ddid,
  d.confid1 AS confid,
  c.pdbcode,
  c.chainid
FROM dyndomrun d
  INNER JOIN conformer c ON d.confid1 = c.id
  INNER JOIN domainregion r ON r.domainid::varchar(8) = d.ddid
UNION ALL
SELECT
  NULL,
  NULL,
  NULL,
  d.ddid,
  d.confid2,
  c.pdbcode,
  c.chainid
FROM dyndomrun d
  INNER JOIN conformer c ON d.confid2 = c.id

回复收藏 0 原文

银河中√捞星星 2024-12-29 23:41:14

这样的事情行不通吗？

select domainid, dombegin, domened, ddid, confid_id, pdbcode, chainid
from (
SELECT r.domainid, r.dombegin, r.domend, d.ddid  
FROM   domainregion AS r JOIN   dyndomrun AS d ON r.domainid::varchar(8) = d.ddid
ORDER  BY r.domainid, d.ddid, r.dombegin, r.domend
) first 
inner join (
(SELECT confid1 as confid_id, conformer.pdbcode, conformer.chainid 
 from dyndomrun JOIN conformer ON dyndomrun.confid1 = conformer.id) 
 UNION
(SELECT confid2 as confid_id, conformer.pdbcode, conformer.chainid
 from dyndomrun JOIN conformer ON dyndomrun.confid2 = conformer.id) 
) second 
on first.ddid=second.confid_id

应该会很慢吧

Wouldn't something like this work?

select domainid, dombegin, domened, ddid, confid_id, pdbcode, chainid
from (
SELECT r.domainid, r.dombegin, r.domend, d.ddid  
FROM   domainregion AS r JOIN   dyndomrun AS d ON r.domainid::varchar(8) = d.ddid
ORDER  BY r.domainid, d.ddid, r.dombegin, r.domend
) first 
inner join (
(SELECT confid1 as confid_id, conformer.pdbcode, conformer.chainid 
 from dyndomrun JOIN conformer ON dyndomrun.confid1 = conformer.id) 
 UNION
(SELECT confid2 as confid_id, conformer.pdbcode, conformer.chainid
 from dyndomrun JOIN conformer ON dyndomrun.confid2 = conformer.id) 
) second 
on first.ddid=second.confid_id

Probably be slow.

回复收藏 0 原文

~没有更多了~