MySQL - Pivoting a one-to-many relationship over 2 tables

Question

I need to pivot a table, it can either be done for a view or dumping data into a pre-existing table already setup as a pivoted table. My main question is knowing how to SELECT and pivot the data.

Sample data

Table 1:

user_id       user_email       first_name       last_name
-------------------------------------------------------------
1             jdoe@huh.com     John             Doe
2             jcarter@aol.com  Jimmy            Carter

Table 2:

user_id       type       job_title       job_description
---------------------------------------------------------
1             Job 1      Some Job        play all day!
1             Job 2      Another Job     all work no play!
2             Job 1      Clown           IT's not funny

PIVOTED TABLE
what i'm trying to achieve is:
I've shortened the table names, and data for ease of use.

uid    email    fname    lname    jtitle-1    jdesc-1    jtitle-2    jdesc-2
----------------------------------------------------------------------------
1      jdoe@>  John     Doe      Some Job    play all>   Another J>  all wo>
2      jcar@>  Jimmy    Carter   Clown       IT's not>   null        null

I've looked around for some examples, but none are exactly what i'm looking for.

MySQL pivot table
http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/

UPDATE
Naturally, soon after posting this i came across a solution.
http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/

This does the trick
MAX(IF(pa.fieldname = ‘size’, pa.fieldvalue, NULL)) AS size

Answer 1

What you're looking for is a Joe Celko (SQL for Smarties)-style pivot, by grouping on the primary table's PK and selectively picking table-2 values based on the relative "type".

SELECT u.user_id AS uid,
    u.user_email AS email,
    u.first_name AS fname,
    u.last_name AS lname,
    MAX(CASE WHEN j.type = 'Job 1' THEN j.job_title ELSE NULL END) AS `jtitle-1`,
    MAX(CASE WHEN j.type = 'Job 1' THEN j.job_description ELSE NULL END) AS `jdesc-1`,
    MAX(CASE WHEN j.type = 'Job 2' THEN j.job_title ELSE NULL END) AS `jtitle-2`,
    MAX(CASE WHEN j.type = 'Job 2' THEN j.job_description ELSE NULL END) AS `jdesc-2`,
    MAX(CASE WHEN j.type = 'Job 3' THEN j.job_title ELSE NULL END) AS `jtitle-3`,
    MAX(CASE WHEN j.type = 'Job 3' THEN j.job_description ELSE NULL END) AS `jdesc-3`,
    MAX(CASE WHEN j.type = 'Job 4' THEN j.job_title ELSE NULL END) AS `jtitle-4`,
    MAX(CASE WHEN j.type = 'Job 4' THEN j.job_description ELSE NULL END) AS `jdesc-4`,
    MAX(CASE WHEN j.type = 'Job 5' THEN j.job_title ELSE NULL END) AS `jtitle-5`,
    MAX(CASE WHEN j.type = 'Job 5' THEN j.job_description ELSE NULL END) AS `jdesc-5`
FROM users AS u
LEFT JOIN jobs AS j ON j.user_id = u.user_id
GROUP BY u.user_id;