MySQL | Complex (for me) SQL Statement, how to?

Question

I have a table that contains posts and another table that contains meta options for each post in first table. The meta options table is a key value pare table.

Lets say I have the posts tables that look like that

Posts
---------------------------------------------
id | Other columns ....
---------------------------------------------
1  | data ...
2  | data ...
3  | data ...
4  | data ...
5  | data ...

and the meta options table that look like that

Meta
---------------------------------------------
id | post_id | meta_key | meta_value
---------------------------------------------
1  | 1       | views    | 5
2  | 1       | maxViews | 100
3  | 2       | views    | 0
4  | 2       | maxViews | 1000
5  | 1       | publisOn | 2013-05-14 10:41
6  | 1       | auhor    | MyUser
7  | 2       | auhor    | Another author

The question is, how can I get the post with id equals to 1 by making a comparison of the meta_key values views and maxViews.

For example, I like to retrive the post id with ID 1 only if the views is lower than the masViews in the Meta table.

Any help please ?

Answer 1

Assuming that (post_id, meta_key) has a UNIQUE constraint, you can join twice to the Meta table:

SELECT 
    p.*,                                          -- replace * with the columns you need
    CAST(m1.meta_value AS SIGNED) AS views,
    CAST(m2.meta_value AS SIGNED) AS maxViews
FROM
    posts AS p
  JOIN
    meta AS m1
      ON  m1.meta_key = 'views'
      AND m1.post_id = p.id
  JOIN
    meta AS m2
      ON  m2.meta_key = 'maxViews'
      AND m2.post_id = p.id
WHERE
    CAST(m1.meta_value AS SIGNED) < CAST(m2.meta_value AS SIGNED) ;

And here is the SQL-Fiddle (thnx to @bluefeet)

Answer 2

You can pivot the data first and then apply your filter. In order to pivot in MySQL you can use an aggregate function with a CASE expression:

select p.id,
  cast(max(case 
           when m.meta_key ='views' 
           then m.meta_value end) as unsigned) Views,
  cast(max(case 
           when m.meta_key ='maxViews' 
           then m.meta_value end) as unsigned) MaxViews  
from posts p
left join meta m
  on p.id = m.post_id
group by p.id

This will convert the data from rows into columns (See SQL Fiddle with Demo):

| ID |  VIEWS | MAXVIEWS |
--------------------------
|  1 |      5 |      100 |
|  2 |      0 |     1000 |
|  3 | (null) |   (null) |
|  4 | (null) |   (null) |
|  5 | (null) |   (null) |

After the data is in the column format you can apply the filter:

select *
from
(
  select p.id,
    cast(max(case 
               when m.meta_key ='views' 
               then m.meta_value end) as unsigned) Views,
    cast(max(case 
               when m.meta_key ='maxViews' 
               then m.meta_value end) as unsigned) MaxViews  
  from posts p
  left join meta m
    on p.id = m.post_id
  group by p.id
) src
where Views < MaxViews;

See SQL Fiddle with Demo. This give the final result of:

| ID | VIEWS | MAXVIEWS |
-------------------------
|  1 |     5 |      100 |
|  2 |     0 |     1000 |