Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Based on your description it seems like you want to <em>pivot</em> the data but unfortunately MySQL does not have a <em>pivot</em> function so you will need to replicate it using an aggregate function with a <code>CASE</code> statement.</p> <p>If you know the description values ahead of time you can hard-code your query to the following:</p> <pre><code>select c.id, c.compname, p.name, p.lastname, max(case when d.description = 'weight' then pd.value end) weight, max(case when d.description = 'haircolor' then pd.value end) haircolor, max(case when d.description = 'height' then pd.value end) height from companies c left join persons p on c.id = p.`company id` left join person_details pd on p.id = pd.`persons id` left join details d on pd.`details id` = d.id -- where p.id in (1, 2) group by c.id, c.compname, p.name, p.lastname </code></pre> <p>See <a href="http://sqlfiddle.com/#!2/5949d/8" rel="nofollow">SQL Fiddle with Demo</a></p> <p>If you have an unknown number of values, then you can use a <a href="http://buysql.com/mysql/14-how-to-automate-pivot-tables.html" rel="nofollow">prepared statement to generate this dynamically</a> similar to this:</p> <pre><code>SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(CASE WHEN d.description = ''', description, ''' then pd.value end) AS ', description ) ) INTO @sql FROM details; SET @sql = CONCAT('SELECT c.id, c.compname, p.name, p.lastname, ', @sql, ' from companies c left join persons p on c.id = p.`company id` left join person_details pd on p.id = pd.`persons id` left join details d on pd.`details id` = d.id -- where p.id in (1, 2) group by c.id, c.compname, p.name, p.lastname'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; </code></pre> <p>See <a href="http://sqlfiddle.com/#!2/5949d/10" rel="nofollow">SQL Fiddle with Demo</a></p> <p>Both versions generate the result:</p> <pre><code>| ID | COMPNAME | NAME | LASTNAME | WEIGHT | HEIGHT | HAIRCOLOR | --------------------------------------------------------------------- | 1 | ACME | donald | duck | 70 | (null) | red | | 1 | ACME | lucky | luke | 90 | (null) | (null) | | 2 | ACME Group | mickey | mouse | (null) | 180 | (null) | </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POmysql pivot table or something
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTaryn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POmysql pivot table or something
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks, that is exactly what I need. You only forgot to add 'where id in (1,3)' after 'from details'. Scratched my head a bit to fully understand what was going on but I've figured it out. Thanks again.
      singulars
      1. PO
      1. USuser1768401
    2. CO@user1768401 I had just commented it out so you could see the entire solution. :)
      singulars
      1. PO
      1. USTaryn
 

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