How can I rewrite this complex SQL query to not use a subquery for MySQL Views?

Question

Well for whatever reason MySQL does not support subqueries in a View. I'm stuck using MySQL for the forseeable future and I'm needing to write a View to aggregate data from an EAV-style table into a more readable format. The View itself is going to act as the Model for a Ruby on Rails-based application (it's read only so I'm safe using a view).

How could I, if I can at all, rewrite this beast to be able to create a view out of it? I suppose if I had to I could just create a series of mini-views and then one master view (one View to rule them all, as the case could be) but I'm wondering there's another way.

Here's the monster; the large numbers of outer joins are because all of the fields are optional but I need to display all of the columns:

select ls.subscriberid as id, l.name as list_name, 
  ifnull(cfn.first_name, '') as first_name, 
  ifnull(cln.last_name, '') as last_name, 
  ifnull(ce.email_address, '') as email_address, 
  ifnull(cm.mobile_phone, '') as mobile_phone
from (
    select ls.subscriberid, ifnull(s.data, '') as first_name
    from subscribers_data s
      right outer join list_subscribers ls on ls.subscriberid = s.subscriberid
    where s.fieldid = 2 /* First Name */
) cfn
right outer join (
    select ls.subscriberid, ifnull(s.data, '') as last_name
    from subscribers_data s
      right outer join list_subscribers ls on ls.subscriberid = s.subscriberid
    where s.fieldid = 3 /* Last Name */
) cln on cfn.subscriberid = cln.subscriberid
right outer join (
    select ls.subscriberid, ls.emailaddress as email_address
    from list_subscribers ls
) ce on ce.subscriberid = cfn.subscriberid
right outer join (
    select ls.subscriberid, ifnull(s.data, '') as mobile_phone
    from subscribers_data s
      right outer join list_subscribers ls on ls.subscriberid = s.subscriberid
    where s.fieldid = 81 /* Mobile Phone */
) cm on cm.subscriberid = cfn.subscriberid
right outer join list_subscribers ls on ls.subscriberid = cfn.subscriberid
inner join lists l on ls.listid = l.listid

Answer 1

try this one:

SELECT ls.subscriberid as id, l.name as list_name, 
       ifnull(group_concat(CASE WHEN s.fieldId = 2 
                                THEN s.data 
                                ELSE NULL 
                           END), '') as first_name, 
       ifnull(group_concat(CASE WHEN s.fieldId = 3
                                THEN s.data 
                                ELSE NULL 
                           END), '') as last_name, 
       ifnull(ls.email_address, '') as email_address, 
       ifnull(group_concat(CASE WHEN s.fieldId = 81
                                THEN s.data 
                                ELSE NULL 
                           END), '') as mobile_phone
  FROM list_subscribers ls
INNER JOIN lists l on ls.listid = l.listid
 LEFT JOIN subscribers_data s on ls.subscriberid = s.subscriberid
  GROUP BY ls.subscriberid, l.name, ls.email_address