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.

score 4 · Accepted Answer · edited Dec 06 '12 at 15:30

4

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'

edited Dec 06 '12 at 15:30

Chris Seymour

83,387
30
160
202

answered Dec 06 '12 at 15:21

Mahmoud Gamal

78,257
17
139
164

I believe that will always return an empty result set except in the case X = Y – Malcolm O'Hare Dec 06 '12 at 15:22
Great this is what I meant! – Chris Seymour Dec 06 '12 at 15:31
Yes, this is the canonical pattern for querying multiple custom fields at once. – hasienda Dec 07 '12 at 20:49

score 0 · Answer 2 · edited Dec 06 '12 at 15:32

0

I this this should do the trick:

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')

edited Dec 06 '12 at 15:32

Chris Seymour

83,387
30
160
202

answered Dec 06 '12 at 15:20

João Simões

1,351
1
10
20

score 0 · Answer 3 · answered Dec 06 '12 at 15:23

0

Maybe

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'

answered Dec 06 '12 at 15:23

Hamlet Hakobyan

32,965
6
52
68

This would display `ticket_custom.value` for `id=$id,name='X'` twice not once for `id=$id,name='X'` and once for `id=$id,name='Y'` – Chris Seymour Dec 06 '12 at 15:34

Join table with composite key twice

3 Answers3

Linked