Reputation: 3
Preemptive apology for the title there... wasn't sure what to put....
I am not the best with mysql and I have a problem that I am not able to wrap my head around.
I have one table....which is the metadata table for a wordpress site... In the table I am storing the FB like count and the permalink for each post.... so it looks something like this for one post...
+-------------+----------------+-----------------+----------------------------------+ | meta_id | post_id | meta_key | meta_value | +-------------+----------------+-----------------+----------------------------------+ | 1 | 446 | _fb_count | 2 | | 2 | 446 | _permalink | /2013/08/image-aligned-left/446/ | +-------------+----------------+-----------------+----------------------------------+
What I would like to do is query this table and return both these values so that the returned data would be (post_id,Facebook Like Count, Permalink)... So it would look like:
+----------------+-----------------+----------------------------------+ | post_id | _fb_count | _permalink | +----------------+-----------------+----------------------------------+ | 446 | 2 | /2013/08/image-aligned-left/446/ | +----------------+-----------------+----------------------------------+
I looked around and am guessing I need to do some kind of inner join on the same table.... but I am not getting the results I want...
I tried something like this with no success:
SELECT pl.post_id,pl.meta_value,FBL.meta_value from wp_postmeta pl inner join wp_postmeta FBL on pl.post_id = FBL.post_id WHERE pl.meta_key = '_permalink' OR FBL.meta_key = '_fb_count
Any help would be appreciated...
Upvotes: 0
Views: 4149
Reputation: 61
Use following query:
SELECT e.post_id, e.meta_value as _fb_count, m.meta_value as _permalink FROM table
e inner join 'table' m where e.meta_key = '_fb_count' and m.meta_key= '_permalink' and e.post_id= m.post_id
Upvotes: 0
Reputation: 92845
You have to use conditional aggregation for that
SELECT post_id,
MAX(CASE WHEN meta_key = '_fb_count' THEN meta_value END) `_fb_count`,
MAX(CASE WHEN meta_key = '_permalink' THEN meta_value END) `_permalink`
FROM wp_postmeta
WHERE meta_key IN('_fb_count', '_permalink')
GROUP BY post_id
Output:
| POST_ID | _FB_COUNT | _PERMALINK | |---------|-----------|----------------------------------| | 446 | 2 | /2013/08/image-aligned-left/446/ |
Here is SQLFiddle demo
But I agree with @Rob you probably better off using WP's appropriate functions for that.
Upvotes: 1