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.