Join table with composite key twice

Question

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.

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

I can do a single join:

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

I need something like:

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'

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

Answer 1

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'