Geek Answers Handbook

Mysql query ignores row if child row to left of join table does not exist

Hi, first of all, I am not that expert in mysql queries. I have tow tables wp_posts and wp_wti_like_post and I do 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 if wp_wti_like_post if there is no row wp_wti_like_post "t even show the line from wp_posts and just ignore it, please help her really need this.

inquiry:

 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

wp_wti_like_post table

http://prntscr.com/6xixrd

wp_posts table

http://prntscr.com/6xixzp

+5
source share
2 answers

So, I sorted it out myself and went to work this way

in place

 SUM( wp_wti_like_post.value ) = NULL

he should be

 SUM( wp_wti_like_post.value ) IS NULL
0
source

You may need to restructure the request 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 retrieves only published wti_like_posts.

Then a left join is performed, which will give you the expected result of extracting all the rows from wp_posts, even if they are not joining the record from the subquery.

After that, the GROUP statement calculates the SUMming values.

In the outermost request, the requirement is applied that the sum be either less than 2 or be zero.

0
source

More articles:


All Articles