Mysql num rows on multiple tables using UNION

Question

Hi all,

I'm trying to find number of rows for two tables: Example 1 returns 81 (which is wrong).

SELECT p_id FROM j_posts 
INNER JOIN j_blogs ON p_blog_id = b_id && b_approved = 1 
WHERE p_is_draft = 0 
UNION SELECT ep_id FROM j_external_posts 
INNER JOIN j_blogs ON ep_blog_id = b_id && b_approved = 1

I have then tried to split up the query in two:

SELECT ep_id FROM j_external_posts INNER JOIN j_blogs ON ep_blog_id = b_id 
&& b_approved = 1

First part of the query returns 70, and the second returns 39. This is the right amount of rows.

SELECT p_id FROM j_posts INNER JOIN j_blogs ON p_blog_id = b_id 
&& b_approved = 1 WHERE p_is_draft = 0

What am I doing wrong in Example 1? It should return 109 instead of 81.

Thanks in advance

Answer 1

I think you must say "UNION ALL" instead of UNION.
UNION Does a implicit DISTINCT so same values are displayed only once..

Answer 2

Use UNION ALL, UNION would return distinct rows