CODEWITHSUNDEEP
mysqlsqlaggregation
crmepham
crmepham

Reputation: 4740

How to get single row of values based on value of another column?

I am attempting to select the values from the value column based on the values in the name column.

For example:

mysql> select name, value from rss_feed_property_value where rss_feed_id = 31;
+-------------+---------------+
| name        | value         |
+-------------+---------------+
| High        | 45            |
| Description | Rain And Snow |
| Low         | 25            |
| Day         | Fri           |
| Date        | 29 Dec 2017   |
+-------------+---------------+
5 rows in set (0.00 sec)

I have almost achieved this with the following query:

select

  if (rfpv.name = 'Date', rfpv.value, null) as `Date`,
  if (rfpv.name = 'High', rfpv.value, null) as `High`,
  if (rfpv.name = 'Low', rfpv.value, null) as `Low`,
  if (rfpv.name = 'Description', rfpv.value, null) as `Description`

from rss_feed rf
join rss_feed_definition rfd on rf.rss_feed_definition_id = rfd.id
join rss_feed_property_value rfpv on rfpv.rss_feed_id = rf.id

where rfd.type = 'weather'
and rf.id = 31
order by rf.id;

Which produces the following output:

+-------------+------+------+---------------+
| Date        | High | Low  | Description   |
+-------------+------+------+---------------+
| NULL        | 45   | NULL | NULL          |
| NULL        | NULL | NULL | Rain And Snow |
| NULL        | NULL | 25   | NULL          |
| NULL        | NULL | NULL | NULL          |
| 29 Dec 2017 | NULL | NULL | NULL          |
+-------------+------+------+---------------+
5 rows in set (0.00 sec)

How can I now flatten the above, by ignoring the null values to leave just a single row like the following:

+-------------+------+------+---------------+
| Date        | High | Low  | Description   |
+-------------+------+------+---------------+
| 29 Dec 2017 | 45   | 25   | Rain and Snow |
+-------------+------+------+---------------+

Upvotes: 0

Views: 27

Answers (3)

MatBailie
MatBailie

Reputation: 86706

Just put MAX() around everything that you want to fold together (MAX() ignores NULL values), and GROUP BY the id you're trying to fold everything in to...

SELECT
  rf.id,
  MAX(if (rfpv.name = 'Date',        rfpv.value, null))  AS `Date`,
  MAX(if (rfpv.name = 'High',        rfpv.value, null))  AS `High`,
  MAX(if (rfpv.name = 'Low',         rfpv.value, null))  AS `Low`,
  MAX(if (rfpv.name = 'Description', rfpv.value, null))  AS `Description`
FROM
  rss_feed                   AS rf
INNER JOIN
  rss_feed_definition        AS rfd
    ON rf.rss_feed_definition_id = rfd.id
INNER JOIN
  rss_feed_property_value    AS rfpv
    ON rfpv.rss_feed_id = rf.id
WHERE
      rfd.type = 'weather'
  AND rf.id = 31
GROUP BY
  rf.id
ORDER BY
  rf.id
;

I added the field rf.id to the SELECT just to make it a little more clear what is being done. (And so also added the associated GROUP BY rf.id)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Aggregate by the rf.id and use conditional aggregation. I would use CASE expressions here as this syntax would be more widely supported across databases than MySQL's IF.

SELECT
    rf.id,
    MAX(CASE WHEN rfpv.name = 'Date'        THEN rfpv.value END) AS Date,
    MAX(CASE WHEN rfpv.name = 'High'        THEN rfpv.value END) AS High,
    MAX(CASE WHEN rfpv.name = 'Low'         THEN rfpv.value END) AS Low,
    MAX(CASE WHEN rfpv.name = 'Description' THEN rfpv.value END) AS Description
FROM rss_feed rf
INNER JOIN rss_feed_definition rfd
    ON rf.rss_feed_definition_id = rfd.id
INNER JOIN rss_feed_property_value rfpv
    ON rfpv.rss_feed_id = rf.id
WHERE
   rfd.type = 'weather' AND
   rf.id = 31
GROUP BY
    rf.id
ORDER BY
    rf.id;

Upvotes: 2

crmepham
crmepham

Reputation: 4740

I achieved this with the following query:

select

  rf.id as rssFeedId,

  (select value
   from rss_feed_property_value
   where name = 'Date'
   and rss_feed_id = rssFeedId) as `Date`,

   (select value
   from rss_feed_property_value
   where name = 'High'
   and rss_feed_id = rssFeedId) as `High`,

   (select value
   from rss_feed_property_value
   where name = 'Low'
   and rss_feed_id = rssFeedId) as `Low`,

   (select value
   from rss_feed_property_value
   where name = 'Description'
   and rss_feed_id = rssFeedId) as `Description`


from rss_feed rf
join rss_feed_definition rfd on rf.rss_feed_definition_id = rfd.id
join rss_feed_property_value rfpv on rfpv.rss_feed_id = rf.id

where rfd.type = 'weather'
and rf.id = 31

group by rf.id

order by rf.id;

I'm not sure if this is the most efficient way of doing it though.

Upvotes: 0

Related Questions