CODEWITHSUNDEEP
mysqlsql
user2562674
user2562674

Reputation: 41

'Compact' SELECT results in mySQL

Is there anyway to 'compact' the results of a SQL query?

The results look like:

+----+-----------+----------+-----------+-------------+--------+
| ID | Firstname | Lastname |   Hobby   |     Job     |  Age   |
+----+-----------+----------+-----------+-------------+--------+
| 1  | John      | Doe      | (null)    | (null)      | 30     |
| 1  | John      | Doe      | Chess     | (null)      | (null) |
| 2  | Adam      | Jackson  | (null)    | Accountant  | (null) |
| 2  | Adam      | Jackson  | (null)    | (null)      | 55     |
| 3  | Michael   | Smith    | Knitting  | (null)      | (null) |
| 3  | Michael   | Smith    | (null)    | Banker      | (null) |
+----+-----------+----------+-----------+-------------+--------+

But I would like it to look like this:

+----+-----------+----------+-----------+-------------+--------+
| ID | Firstname | Lastname |   Hobby   |     Job     |  Age   |
+----+-----------+----------+-----------+-------------+--------+
| 1  | John      | Doe      | Chess     | (null)      | 30     |
| 2  | Adam      | Jackson  | (null)    | Accountant  | 55     |
| 3  | Michael   | Smith    | Knitting  | Banker      | (null) |
+----+-----------+----------+-----------+-------------+--------+

I tried using GROUP BY but it only accepts the first custom field from the database for each person.

I've setup a SQL Fiddle here: http://sqlfiddle.com/#!9/39563/2

The setup is a little odd but I need to keep that structure due to the database I'm working on.

Upvotes: 0

Views: 581

Answers (3)

dnoeth
dnoeth

Reputation: 60472

You should move the MAX-calculation into a Derived Table to apply aggregation before the Join. This will increase performance:

select p.id as "User ID", p.firstname Firstname, p.lastname as Lastname,
cf.Hobby,
cf.Job,
cf.Age
from 
 (
   select cfv.relid,
      max(case when cf.fieldname = "Hobby" then cfv.value end) as "Hobby",
      max(case when cf.fieldname = "Job" then cfv.value end) as "Job",
      max(case when cf.fieldname = "Age" then cfv.value end) as "Age"
   from CustomFields as cf inner join CustomFieldValues as cfv
     on cfv.fieldid = cf.id
   where cf.fieldname = "Hobby"
      or cf.fieldname = "Job"
      or cf.fieldname = "Age"
   group by cfv.relid
/* -- apply additional conditions here, e.g. "all three fields must be set"
   having Hobby is not null 
      and Job is not null
      and age is not null 
*/
 ) as cf
inner join People as p
   on cf.relid = p.id
order by p.id

fiddle

Upvotes: 0

Rasika
Rasika

Reputation: 1998

Use the group_concat function in your group by function for string values and the max function for numeric fields.

SELECT id, FirstName, LastName, 
   GROUP_CONCAT(CASE WHEN Hobby IS NOT NULL THEN Hobby END) AS Hobby,
   GROUP_CONCAT(CASE WHEN Job IS NOT NULL THEN Job END AS Job, 
   MAX(Age) AS Age 
FROM TableGROUP BY id

Upvotes: 0

jpw
jpw

Reputation: 44891

You can use the max aggregate function an group by to flatten the result like this:

select 
  p.id as "User ID", 
  p.firstname Firstname, 
  p.lastname as Lastname,
  max(case when cf.fieldname = 'Hobby' then cfv.value end) as "Hobby",
  max(case when cf.fieldname = 'Job'   then cfv.value end) as "Job",
  max(case when cf.fieldname = 'Age'   then cfv.value end) as "Age"
from CustomFields cf 
join CustomFieldValues cfv on cfv.fieldid = cf.id
join People p on cfv.relid = p.id
where cf.fieldname in ('Hobby', 'Job', 'Age')
group by p.id, p.firstname, p.lastname 
order by p.id;

Also, string literals should be enclosed in single-quotes, and you can use aliases to reduce the query text and make it more readable.

Your Fiddle, updated

Upvotes: 2

Related Questions