MySQL, merging 2 or more tables before execute SELECT DISTINCT query?
I want to calculate how many unique logins from 2 (or probably more tables).
I tried this:
SELECT count(distinct(l1.user_id))
FROM `log_1` l1
LEFT JOIN `log_2` l2
ON l1.userid = l2.userid;
But it gives me result of l1. If I didn't put l1 on li.userid (distinct), it said "ambiguous".
How do I combine the table, and then select unique login of the combined table?
EDIT:
Tested: I test t开发者_StackOverflow社区he count(distinct(l1.userid)) and count(distinct(l2.userid)). It gives me different result
If you are using LEFT JOIN
then you will get at least one row in the combined result for each row in l1, so the join is entirely unnecessary if you just want a distinct count. This would give you the same result as your query:
SELECT count(distinct(l1.user_id))
FROM `log_1` l1
Perhaps you want an INNER JOIN
or UNION
instead? A UNION
will count a user if they appear in either table. An INNER JOIN
will count them only if they appear in both tables. Here's an example of the UNION
:
SELECT count(*) FROM (
SELECT distinct(user_id) FROM `log_1`
UNION
SELECT distinct(user_id) FROM `log_2`
) T1
精彩评论