Join SQL help
Can someone help me out with this SQL?
SELECT A.post_id, B.post_id, A.ul_value as "likes", B.ul_value as "dislikes"
FROM wp_like_dislike_counters as A,
wp_like_dislike_counters as B
where
A.post_id = B.post_id
and A.ul_key = 'u_like'
and B.ul_key = 'u_dislike'
The results I'm getting:
3, 3, 1, 2
4, 4, 3, 2
The results I want:
3, 3, 1, 2
4, 4, 3, 2
1, 1, 0, 1
Below is the data in the table. Notice that the row with id of 32 has a post_id of 1 along with 1 for the l_value (meaning 1 dislike). Since this post_id has no row for "likes", I would like to display this as a 0 as in the results I want above. Is this possible with this table structure?
id | post_id | l_key | l_value
35 3 u_dislike 2
34 4 u_dislike 2
31 4 u_like | 3
32 1 u_dislike | 1
36 3 u_like | 1