Databases
StackOverflow2013
Postsdbo
POMySQL column names as field names for a many to many relationship with a junction table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL column names as field names for a many to many relationship with a junction table
    primarykey
    data
    text
    <p>bit of a complicated one here. I've been looking around at a way to use results as column names, and whilst it seems possible, all the examples I've found use a very simple table design.</p> <p>What I've got is 3 tables.</p> <p>We have an 'order' table, which can then have 'order_extras'. The 'extras' table stores the names and prices of the extras, and the 'order_extras' basically cointains a primary key, the order id and the extra id. </p> <p>A rough graphical representation of this is as follows:<img src="https://i.stack.imgur.com/KGzIF.png" alt="enter image description here"></p> <p>Using this as an example, lets assume the 'extras' table is populated with 3 extra items, the name and price are irrelevant at this stage.</p> <p>What I want to do, is get all the orders, but with extra columns for the name of each extra item. If the item has been purchased (aka linked in the order_extras table) it'll show the price, otherwise it'll be empty/null.</p> <p>Is this even possible? I've been looking into pivot tables but the information on this sort of thing seems a bit shaky. Any info or suggestions would be greatly appreciated!</p> <p><strong>Example data</strong></p> <p>Extras:</p> <pre><code>+----+------------------+--------+ | id | name | price | +----+------------------+--------+ | 1 | Insurance | 59.95 | | 2 | Lifetime Updates | 79.95 | | 3 | Phone Support | 124.95 | +----+------------------+--------+ </code></pre> <p>Orders:</p> <pre><code>+----+------------+ | id | customer | +----+------------+ | 1 | John Smith | | 2 | Bob Newbie | | 3 | Bill Jobs | | 4 | Ray Stantz | +----+------------+ </code></pre> <p>order_extras:</p> <pre><code>+----+----------+----------+ | id | order_id | extra_id | +----+----------+----------+ | 1 | 4 | 2 | | 2 | 3 | 1 | | 3 | 3 | 3 | | 4 | 1 | 1 | +----+----------+----------+ </code></pre> <p>Desired Output:</p> <pre><code>+----------+----------------+-----------+------------------+---------------+ | order.id | order.customer | Insurance | Lifetime Updates | Phone Support | +----------+----------------+-----------+------------------+---------------+ | 1 | John Smith | 59.95 | 0 | 0 | | 2 | Bob Newbie | 0 | 0 | 0 | | 3 | Bill Jobs | 59.95 | 0 | 124.95 | | 4 | Ray Stantz | 0 | 79.95 | 0 | +----------+----------------+-----------+------------------+---------------+ </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSk446
    1. USSk446
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL column names as field names for a many to many relationship with a junction table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL column names as field names for a many to many relationship with a junction table
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL column names as field names for a many to many relationship with a junction table
      1. USgSaenz
      1. VTFavorite
    1. COCan you post some sample data and the desired result? It might be easier to understand what you are doing without reading the word problem. :)
      singulars
      1. POMySQL column names as field names for a many to many relationship with a junction table
      1. USTaryn
    2. COFrom what I Can tell, MySQL doesn't support any sort of pivot keyword or "true" pivoting at all. Take a look at this post. This guy wrote a crazy sproc to handle it: http://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data
      singulars
      1. POMySQL column names as field names for a many to many relationship with a junction table
      1. USAdam Plocher
    3. COJust updated the first post with some examples of what I'm hoping to do
      singulars
      1. POMySQL column names as field names for a many to many relationship with a junction table
      1. USSk446
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload