Please help a coder in distress... Complicated query is giving me trouble


#1

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.


#2

You should probably start with learning how to do a proper join. The manual is always a great place to start. https://dev.mysql.com/doc/refman/8.0/en/join.html

You can try this as well.
http://www.mysqltutorial.org/mysql-join/

Or https://www.techonthenet.com/mysql/joins.php