0

I need to make more inner joins to have a table with value in column (instead of many records).

So instead of this:

SELECT U.id,
       U.name,
       FV1.value AS Azienda,
       FV2.value AS AreaManager,
       FV3.value AS Tipologia
FROM `kizgv_users` U
JOIN `kizgv_fields_values` FV1
ON FV1.item_id = U.id AND FV1.field_id = 8
JOIN `kizgv_fields_values` FV2
ON FV2.item_id = U.id AND FV2.field_id = 29
JOIN `kizgv_fields_values` FV3
ON FV3.item_id = U.id AND FV3.field_id = 33

I'm trying to do this in order to improve performances since it goes in 500 error

SELECT U.id, U.name, FV1.value as Azienda, FV2.value as AreaManager, FV3.value as Tipologia
FROM kizgv_users U
CASE 
'kizgv_fields_values'.'field_id'
WHEN 
8
THEN
(JOIN kizgv_fields_values FV1 on FV1.item_id = U.id)
WHEN 
29
THEN
(JOIN kizgv_fields_values FV2 on FV2.item_id = U.id)
WHEN 
33
THEN
(JOIN kizgv_fields_values FV3 on FV3.item_id = U.id)

@RaymondNijland thanks, I've tried : stackoverflow.com/questions/7674786/mysql-pivot-table and this came out

 create view omni as (
  select
    kizgv_fields_values.*,
    case when field_id = "8" then field_id end as Azienda,
    case when field_id = "29" then field_id end as AreaManager,
    case when field_id = "33" then field_id end as Tipo
  from kizgv_fields_values
);

select * from kizgv_users;

create view pivot as (
  select
    item_id,
    sum(Azienda) as Azienda,
    sum(AreaManager) as AreaManager,
    sum(Tipo) as Tipo
  from omni
  group by item_id
);

select * from pivot;

create view finale as (
  select 
    item_id,
    coalesce(Azienda, 0) as Azienda, 
    coalesce(AreaManager, 0) as AreaManager, 
    coalesce(Tipo, 0) as Tipo
  from pivot 
);

select * from finale;

Still, I have not improvement in performances comparing with multiple queries

Miriam M.
  • 1
  • 2
  • There is a better way to do this.. Something like https://stackoverflow.com/questions/7674786/mysql-pivot-table or this https://stackoverflow.com/questions/1241178/mysql-rows-to-columns – Raymond Nijland Jul 13 '18 at 12:05
  • 1
    Could you clarify a little more what you’re trying to do? Are you saying that your multiple joins query does what you want, you’re just looking for a different way to do it? if so, you might use a PIVOT. Otherwise, I’m not sure what you’re asking, sorry. – matt.dolfin Jul 13 '18 at 12:28
  • Thanks everybody! – Miriam M. Jul 13 '18 at 12:49
  • Yes, multiple joins query does what I want, I'm just looking for a different way to do it because it turns out an 500 error when I perform it. So I'm looking for a more performant way. – Miriam M. Jul 13 '18 at 12:51

0 Answers0