开发者

MySQL get query from query

I have the following MySQL query that gets me the meta_key equal to _cnote_module_number and the post_id equal to $mod_id.

But now how do i add onto that query after i get the post_id = '$mod_id' to look at the result and find the meta_value (which is a number) and than to look back into meta_key equal to _cnote_module_number and get the meta_value thats greater than the meta_value开发者_JAVA百科 from the first result.

$query = ( "SELECT * FROM postmeta WHERE meta_key = '_cnote_module_number' AND post_id = '$mod_id'" );

EDIT

I have 2 tables the first is postmeta, with..

-------------------------------------
 post_id  |  meta_key  |  meta_value
-------------------------------------
    10    |   jason    |     1
    49    |   cnote    |     3
    56    |   cnote    |     2
    97    |   cnote    |     7

The second is usermeta with..

-----------
  mod_id  
-----------
    10    
    49    
    67   

I want to make sure that postmeta.meta_key = 'cnote' and usermeta.mod_id is equal to postmeta.post_id. Than whichever ID was equivalent i want to get the postmeta.meta_value and than find the first number greater than that value.

In this example i would like the result to be the 4th row from the postmeta


You can JOIN a table to itself by giving it two different names. For example, the following would return all meta_values where the B table's meta_value was greater than the A table's meta_value:

SELECT B.meta_value
FROM postmeta A
JOIN postmeta B
ON (    B.meta_value > A.meta_value
    AND A.meta_key = B.meta_key)
WHERE A.meta_key = 'cnote'
AND A.post_id = '$mod_id'

But for your specific question, we want the next meta_value. That's even easier; since you are only wanting to return one row, you can take the MIN of the meta_values from above:

SELECT MIN(B.meta_value)
FROM postmeta A
JOIN postmeta B
ON (    B.meta_value > A.meta_value
    AND A.meta_key = B.meta_key)
WHERE A.meta_key = 'cnote'
AND A.post_id = '$mod_id'

If you want to get a query that returns all pairs of meta_values where A.meta_value immediately precedes B.meta_value, we can do that too; let me know.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜