CODEWITHSUNDEEP
mysqlsqldatabasejointrac
Chris Seymour
Chris Seymour

Reputation: 85865

Join table with composite key twice

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.

Upvotes: 0

Views: 6320

Answers (3)

João Simões
João Simões

Reputation: 1361

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

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

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'

Upvotes: 4

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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'

Upvotes: 0

Related Questions