Rows missing from mysql pivot query results

Question

I have a mysql query as stated below, it returns exactly the results I want for one row, but doesn't return any other rows where I expect there to be 8 in my test data (there are 8 unique test ids). I was inspired by this answer but obviously messed up my implementation, does anyone see where I'm going wrong?

SELECT 
c.first_name,
c.last_name,
n.test_name,
e.doc_name,
e.email,
e.lab_id,
a.test_id,
a.date_req,
a.date_approved,
a.accepts_terms,
a.res_value,
a.reason,
a.test_type,
a.date_collected,
a.date_received,
k.kind_name,
sum(case when metabolite_name = "Creatinine" then t.res_val end) as Creatinine,
sum(case when metabolite_name = "Glucose" then t.res_val end) as Glucose,
sum(case when metabolite_name = "pH" then t.res_val end) as pH
   FROM test_requisitions      AS a
   INNER JOIN personal_info    AS c   ON (a.user_id = c.user_id)
   INNER JOIN test_types       AS d   ON (a.test_type = d.test_type)
   INNER JOIN kinds            AS k   ON (k.id = d.kind_id)
   INNER JOIN test_names       AS n   ON (d.name_id = n.id)
   INNER JOIN docs             AS e   ON (a.doc_id = e.id)
   INNER JOIN test_metabolites AS t   ON (t.test_id = a.test_id)
   RIGHT JOIN metabolites      AS m   ON (m.id = t.metabolite_id)
   GROUP BY a.test_id
   ORDER BY (a.date_approved IS NOT NULL),(a.res_value IS NOT NULL), a.date_req, c.last_name ASC;

You are using `RIGHT JOIN metabolites ` that means your resultset will not contain any rows that don't have a record in `metabolites` table — a1ex07, May 01 '13 at 18:32

Gordon Linoff · Accepted Answer · 2013-05-01T19:07:42.960

1

Most of your joins are inner joins. The last is a right outer join. As written, the query keeps all the metabolites, but not necessarily all the tests.

I would suggest that you change them all to left outer joins, because you want to keep all the rows in the first table:

   FROM test_requisitions      AS a
   LEFT JOIN personal_info    AS c   ON (a.user_id = c.user_id)
   LEFT JOIN test_types       AS d   ON (a.test_type = d.test_type)
   LEFT JOIN kinds            AS k   ON (k.id = d.kind_id)
   LEFT JOIN test_names       AS n   ON (d.name_id = n.id)
   LEFT JOIN docs             AS e   ON (a.doc_id = e.id)
   LEFT JOIN test_metabolites AS t   ON (t.test_id = a.test_id)
   LEFT JOIN metabolites      AS m   ON (m.id = t.metabolite_id)

I would also suggest that your aliases be related to the table, so tr for test_requisition, pi for personal_info, and so on.

edited May 01 '13 at 19:07

answered May 01 '13 at 18:32

Gordon Linoff

1,242,037
58
646
786

You pointed me on the right path, I actually wanted something where all the rows of test_requisitions (and everything joined to it except the metabolite tables) would be duplicated for each metabolite row and then if possible values of test_metabolites would be added or nulls otherwise – Camden Narzt May 01 '13 at 19:01
@CamdenNarzt . . . Glad I was able to help. – Gordon Linoff May 01 '13 at 19:07
I'll accept your answer but I'll add the note that I needed to do an unconditional cross join of the test rows to the metabolites, then a left join to the test_metabolites – Camden Narzt May 01 '13 at 19:12

Rows missing from mysql pivot query results

1 Answers1