Mysql query ignoring the row if child row from left join table doesn’t exists

1 Views

Hi there so first of all , i’m not that expert in mysql queries. i have tow tables wp_posts and wp_wti_like_post and i’m doing LEFT JOIN on wp_wti_like_post ON wp_posts.ID = wp_wti_like_post.post_id and SUM(wp_wti_like_post.value) < 2 BUT if there is no rowin wp_wti_like_post with id of post from wp_posts then it doesn’t show even the row from wp_posts and just ignore it , please help in this really need it.

query:

Read More
SELECT * 
FROM wp_posts
LEFT JOIN wp_wti_like_post ON wp_posts.ID = wp_wti_like_post.post_id
WHERE wp_posts.post_status =  'publish'
GROUP BY wp_wti_like_post.post_id
HAVING SUM( wp_wti_like_post.value ) <2
OR SUM( wp_wti_like_post.value ) = NULL 
LIMIT 0 , 200

table wp_wti_like_post

http://prntscr.com/6xixrd

table wp_posts

http://prntscr.com/6xixzp

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. You might need to restructure your query to as follows:

    SELECT * FROM
(
    SELECT WP.*, SUM(LP.value) AS `value`
    FROM wp_posts WP
    LEFT JOIN 
        (SELECT post_id FROM wp_wti_like_post WHERE post_status = 'publish') LP
        ON WP.ID = LP.post_id
    GROUP BY WP.ID
) T1
WHERE T1.value IS NULL
OR T1.value < 2;

    The Inner-most query first fetches only wti_like_posts that have been published.

    The left-join is then performed, which will give you the expected result of fetching all rows from wp_posts, even if they do not join with a record from the sub-query.

    After that is performed, the GROUP statement performs the computation of SUMming the values.

    The outer-most query enforces the requirement that the sum must either be less than 2 or be null.

  2. So i sorted this out by my self and go it working in this way

    at the place of 

    SUM( wp_wti_like_post.value ) = NULL

    it should need to be 

    SUM( wp_wti_like_post.value ) IS NULL