CODEWITHSUNDEEP
mysqlsqlphpmyadmin
twan
twan

Reputation: 2659

Group values that have the same name in one column and same id in other column

I have the following situation:

With my current query this is how I get my data:

enter image description here

The problem is that I want to combine straat and plaatsnaam and read them as one row. Each straat has the same value_item_id as its plaatsnaam counterpart.

Is it possible to take straat and plaatsnaam and group them together by their value_item_id ?

My current query looks like this:

SELECT fields.id as field_id,
       fields.name,
       fields_categories.field_id as catfield_id,
       fields_values.field_id as fieldvalue_id,
       fields_values.item_id as value_item_id,
       fields_values.value,
       content.id as content_id
FROM snm_fields fields
INNER JOIN snm_fields_categories fields_categories
ON fields.id = fields_categories.field_id
INNER JOIN snm_fields_values fields_values
ON fields_categories.field_id = fields_values.field_id
INNER JOIN snm_content content
ON content.id = fields_values.item_id
WHERE fields.name in ('straat', 'plaatsnaam')

In the end I want to have Ridderstraat 5 and Heenvliet in the same row. I know this can also be done with PHP, but I think doing this directly with SQL is better.

Upvotes: 0

Views: 2948

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

It seems you simply want the plaats and straat per item_id. If so, it suffices to read from tables snm_fields and snm_fields_values only. Using conditional aggregation per item_id:

select 
  fv.item_id, 
  max(case when f.name = 'plaatsnaam' then fv.value end) as plaats,
  max(case when f.name = 'straat' then fv.value end) as straat
from snm_fields_values fv 
join snm_fields f on f.id = fv.field_id
group by fv.item_id
order by fv.item_id, plaats, straat;

Or, if it's okay for you to have your query know the IDs, even:

select 
  item_id, 
  max(case when field_id = 3 then value end) as plaats,
  max(case when field_id = 1 then value end) as straat
from snm_fields_values
group by item_id
order by item_id, plaats, straat;

Upvotes: 1

StanislavL
StanislavL

Reputation: 57401

Use GROUP_CONCAT function.

SELECT value_item_id,
      GROUP_CONCAT(name SEPARATOR ' ') as grouped_name,
      GROUP_CONCAT(value SEPARATOR ' ') as grouped_value,
FROM (the_query) src
GROUP BY value_item_id

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a join on the filtered tables 

  SELECT 
        fields1.id as field_id
      , fields1.name
      , fields1_categories.field_id as catfield_id
      , fields1_values.field_id as fieldvalue_id
      , fields1_values.item_id as value_item_id
      , fields1_values.value
      , content.id as content_id
      , fields2.id as field_id
      , fields2.name
      , fields2_categories.field_id as catfield_id
      , fields2_values.field_id as fieldvalue_id
      , fields2_values.item_id as value_item_id
      , fields2_values.value
  FROM snm_fields fields1
  INNER JOIN (
      SELECT 
            fields.id as field_id
          , fields.name
          , fields_categories.field_id as catfield_id
          , fields_values.field_id as fieldvalue_id
          , fields_values.item_id as value_item_id
          , fields_values.value
      FROM snm_fields fields
      WHERE fields.name  = 'plaatsnaam')

    ) on field2  ON field1.value_item_id = field2.value_item_id
  INNER JOIN snm_fields_categories fields_categories ON fields.id = fields_categories.field_id
  INNER JOIN snm_content content
  WHERE fields.name = 'straat'

Upvotes: 1

Related Questions