用复合键联接表两次 [英] Join table with composite key twice

问题描述

给出具有主键id的表ticket和具有复合键ticket,name的表ticket_custom的方法，如何加入id = ticket and name=X and id = ticket and name=Y.

Given the table ticket with the primary key id and the table ticket_custom with the composite key ticket,name how can I join for id = ticket and name=X and id = ticket and name=Y.

表ticket_custom允许对票证表进行扩展，它具有字段ticket,name,value.

The table ticket_custom allows the ticket table to be extended, it has the fields ticket,name,value.

我可以进行单个加入:

SELECT id, summary, owner, ticket_custom.value
FROM ticket
INNER JOIN ticket_custom
ON id=ticket_custom.ticket AND ticket_custom.name='X'

我需要类似的东西:

SELECT id, summary, owner, ticket_custom.value, ticket_custom.value
FROM ticket
INNER JOIN ticket_custom
ON id=ticket_custom.ticket AND ticket_custom.name='X' AND ticket_custom.name='Y'

第一个ticket_custom.value是id,x的值，第二个是id,y的值.

Where the first ticket_custom.value is the value for id,x and the second is for id,y.

推荐答案

如果我正确理解，这就是您要寻找的东西:

If I understand correctly, this is what you are looking for:

SELECT id, summary, owner, c1.value, c2.value
FROM ticket t
INNER JOIN ticket_custom c1  ON t.id = c1.ticket AND c1.name = 'X'
INNER JOIN ticket_custom c2  ON t.id = c2.ticket AND c2.name = 'Y'

这篇关于用复合键联接表两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！