60579

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 <em>extended</em>, 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.

Answer1:

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'

Answer2:

<strong>Maybe</strong>

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'

Answer3:

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

Recommend

  • how to insert data into multiple tables through ItemWriter
  • unexpected result iterating over a boost::python vector_indexing_suite
  • grep: matching on literal “+”
  • Expand header row into multiple child rows
  • Design of Service Layer and Repositories in Microsoft MVC
  • ELMAH: Can you set it up to email errors only remotely?
  • Express.js : POST data as KEY of a req.body object instead of VALUE of req.body?
  • f:param to composite components
  • Cassandra 2.1: Recursion by nesting UDT's
  • How to format code on aptana 3?
  • R h2o.glm - issue with max_active_predictors
  • AlertDialog style when using setView()
  • Hector: how to query parts of a Composite Type
  • Set focus to first invalid form element in AngularJS
  • Find group of records that match multiple values
  • Why Encoding.ASCII != ASCIIEncoding.Default in C#?
  • Why querying a date BC is changed to AD in Java?
  • Database structure design with variable amounts of fields
  • Django: Count of Group Elements
  • How can I send an e-mail from a vbs script
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Is calc() supported in html email?
  • Can Jackson SerializationFeature be overridden per field or class?
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • Redux, normalised entities and lodash merge
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • Do create extension work in single-user mode in postgres?
  • How to get next/previous record number?
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • PHP: When would you need the self:: keyword?
  • How do you join a server to an Active Directory (domain)?
  • Trying to get generic when generic is not available
  • how does django model after text[] in postgresql [duplicate]
  • Memory offsets in inline assembly
  • How does Linux kernel interrupt the application?
  • Recursive/Hierarchical Query Using Postgres