Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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)

share|improve this answer
1  
I was working on this version as well when you posted. Here is a SQL Fiddle with a demo -- sqlfiddle.com/#!2/7ef63/14 –  bluefeet Feb 8 '13 at 10:16

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 |
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.