phpmyadmin Question posted in
The official documentation can be found here.

I'm not getting correct data by joining two tables – Phpmyadmin

I have two tables users and radacct, both table has same column username .

table radacct also has a column acctstoptime which is important here.

table acctstoptime is actually a datetime column with allow null.

table users has more than 50k records (usernames) but table radacct has variation in records between 12k to 20k

i also has a application which insert or remove data from table radacct and update column acctstoptime in it, so acctstoptime null means username is active/connected and when it has a timestamp it means username is not connected.

Note: This is simplest way of explaining without any complexity. Its a freeradius application: https://en.wikipedia.org/wiki/FreeRADIUS

So when i write this query:

SELECT u.username, u.expiration
FROM users u JOIN radacct r ON u.username=r.username 
WHERE r.acctstoptime IS NULL

I get online/connected username. but what i want is to get offline/not connected username list.

When i write this query:

SELECT u.username, u.expiration 
FROM users u
 LEFT JOIN radacct r ON u.username=r.username
 WHERE r.acctstoptime IS NOT NULL

I get offline customers but not full list joined by users table. Some of them also has another entry in radacct table which has r.acctstoptime NULL so firstly i can not get uniqueness, this query only shows not null query that are present in radacct table.

I like to explain further that:

radacct table has multiple entries with same username but with multiple acctstoptime, only one record can be NULL but other records may vary with different datetime

So i want that i get all username that does not have acctstoptime null, it also has to be unique so only one latest record that is not null

https://ibb.co/Yb84T27

In Really simple words if you understand how freeradius works: I want to get offline customers whose account are active/on/recharged

Tags:

3

Answers


  1. 0

    Use not exists:

    select u.*
from users u
where not exists (select 1
                  from radacct r 
                  where r.username = u.username and
                        r.acctstoptime is null
                 );
    Login or Signup to reply.
  2. 0

    It’s not a perfect script but here’s a possible solution:
    Get a list of all the radacct that are null and filter those out of the user table.

    SELECT DISTINCT u.username, u.expiration
FROM users u
WHERE u.username NOT IN (SELECT username FROM radacct r
 WHERE r.acctstoptime IS NULL)

    Otherwise I would suggest you split the problem in solvable pieces.

    Login or Signup to reply.
  3. 0

    Use aggregation and set the condition in the HAVING clause:

    SELECT u.username, u.expiration
FROM users u JOIN radacct r 
ON u.username = r.username 
GROUP BY u.username, u.expiration
HAVING MAX(r.acctstoptime IS NULL) = 0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search