I’m trying to SELECT * from one table, LEFT JOIN with a row of cells from another table, and also use a SELECT SUM(mycolumn) group by other_tables_username query all in one statement. This is my attempt in two separate queries, but I need them in one query, because I need to sum up a column of cells grouped by username.
I’m working with two databases so I have to use qualified names.
Both the queries are working on their own, but joining them together is crazy.
SELECT SUM(db1.acctsessiontime) totalSessionTime,
db1.username raduid
FROM radius.radacct db1
LEFT JOIN (SELECT db2.username mikuid
FROM mikrotik.customers db2) mikalias
ON db1.username = mikuid
GROUP BY raduid
SELECT *,
db2.username cuid
FROM mikrotik.customers db2
LEFT JOIN (SELECT * FROM radius.radacct db1
INNER JOIN (SELECT MAX(radacctid) id FROM radius.radacct
GROUP BY username) radalias
ON rad.radacctid = radalias.id)
leftjoinalias ON db2.username = leftjoinalias.username
WHERE db2.username='$uid'
Attempt at joining them produces mysql errors (syntax errors):
SELECT *,
db2.username cuid
FROM customers db2
LEFT JOIN (SELECT * FROM radius.radacct db1
INNER JOIN (SELECT MAX(radacctid) id
FROM radius.radacct
GROUP BY username) radalias
ON db1.radacctid = radalias.id) leftjoinalias
ON db2.username = leftjoinalias.username
WHERE db2.username='$uid'
LEFT JOIN (
SELECT SUM(db1.acctsessiontime) totalSessionTime, db1.username raduid
FROM radius.radacct db1
LEFT JOIN (SELECT db2.username mikuid
FROM mikrotik.customers db2) mikalias
ON db1.username = mikuid
GROUP BY raduid
) newalias
ON db1.username = db2.username
GROUP BY db1.username
To reiterate, I have a database (database 1) that acts as a session-log, so each time a user logs in, the time and duration (session) is captured into the db.
I want to fetch all the columns named ‘acctsessiontime’, grouped by the username, from database1. The query must be summed so that a user can see his total session time since he signed up, and not just his latest session time.
I also want to fetch the last (highest) ID of that username to capture his ‘‘last seen’’.
The second database (database 2) is populated with each user’s details like name, address, contact. The two databases are joined with LEFT JOIN's to show a user all of his details on one page.
The queries are working, but separately. In order to display all the data at once I need to join the two queries and am having trouble doing so. Please kindly help, any guidelines would be much appreciated.