Join two tables to get the number of total access for user in MySql

Hi all, hope in your help.

In my MySql I have two tables: tblN1 and tblN2.

The table tblN1 is an old table where are memorized account access in restricted area on the website.

The table tblN2 is a new table where are memorized account access in restricted area on the website.

If try this query on tblN1 :

mysql> SELECT
    COUNT(*) AS TotalN1
FROM
    `tblN1`
WHERE
    UserN1 IN ('7047505')
AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE();
+---------+
| TotalN1 |
+---------+
|       4 |
+---------+
1 row in set

The return of this query is 4.

If try this query on tblN2 :

mysql> SELECT
    COUNT(*) as TotalN2
FROM
    `tblN2`
WHERE
    UserN2 IN ('7047505')
AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE();
+---------+
| TotalN2 |
+---------+
|       0 |
+---------+
1 row in set

The return of this query is 0.

I’m trying to join two tables to get the number of total access for user and update the table tblNCC.

UPDATE tblNCC AS CC
JOIN (
	SELECT
		sum(total) AS Total
	FROM
		(
			SELECT
				COUNT(*) AS Total,
				UserN1
			FROM
				`tblN1`
			WHERE
				UserN1 IN ('7047505')
			AND dateN1 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
			AND CURDATE()
			UNION ALL
				SELECT
					COUNT(*),
					UserN2
				FROM
					`tblN2`
				WHERE
					UserN2 ('7047505')
				AND dateN2 BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
				AND CURDATE()
		) AS t
) AS q ON t.UserN1 = CC.UserNcc
SET CC.Total = t.Total;


[Err] 1054 - Unknown column 't.UserN1' in 'on clause'

But I have this error, why?

Please help me, thank you in advance.

you’re getting that error because columns of the “t” subquery are not visible outside of the enclosing “q” subquery

also, you forgot some GROUP BYs

UPDATE tblNCC AS CC
INNER
  JOIN ( SELECT t.theuser
              , SUM(t.subtotal) AS Total
          FROM ( SELECT UserN1 AS theuser
                      , COUNT(*) AS subtotal
                   FROM tblN1
                  WHERE UserN1 IN ('7047505')
                    AND dateN1 BETWEEN CURRENT_DATE 
                                     - INTERVAL DAYOFYEAR(CURRENT_DATE)+1 DAY
                                   AND CURRENT_DATE
                 GROUP
                     BY UserN1
                 UNION ALL
                 SELECT UserN2 AS theuser
                      , COUNT(*) AS subtotal
                   FROM tblN2
                  WHERE UserN2 IN ('7047505')
                    AND dateN2 BETWEEN CURRENT_DATE 
                                     - INTERVAL DAYOFYEAR(CURRENT_DATE)+1 DAY
                                   AND CURRENT_DATE
                 GROUP
                     BY UserN2
               ) AS t
         GROUP
             BY t.theuser
       ) AS q 
    ON CC.UserNcc = q.theuser
   SET CC.Total = q.Total

Thank you so much for help!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.