加入表复合键两次(Join table with composite key twice)

鉴于表ticket与主键id和表ticket_custom与复合键ticket,name我怎么能加入为id = ticket and name=X and id = ticket and name=Y 。

该表ticket_custom允许票表进行扩展 ，它有场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 。

Answer 1:

如果我理解正确的话，这是你在找什么：

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'

Answer 2:

也许

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' 
    OR id=ticket_custom.ticket AND ticket_custom.name='Y'

Answer 3:

我这个本应该做的伎俩：

SELECT id, summary, owner, ticket_custom.value, ticket_custom.value
FROM ticket
INNER JOIN ticket_custom
ON ticket.id=ticket_custom.ticket
WHERE (ticket_custom.name='X' OR ticket_custom.name='Y')

文章来源: Join table with composite key twice