Join multiple rows of data to query

Question

I am trying to join multiple rows of data onto a sql query.

My database consists of a few tables: clients, client_site_ip, and ip_accounting.

client_site_ip table has the following rows: siteid, userid, site_name and site_ip.

ip_accounting table has the following rows: id, src_address, dst_address, bytes and timeanddate.

clients table has the following rows: userid, username ...... and ipaddress

I am wanting clients to have multiple IP addresses so I have created the table client_site_ip

I currently have this query that separates the IP addresses and everything by a comma but need it separated by rows of new data not commas:

SELECT 
    clients.userid, 
    clients.username, 
    clients.datapackage, 
    client_site_ip.userid, 
    GROUP_CONCAT(client_site_ip.site_name) AS sitenames, 
    GROUP_CONCAT(client_site_ip.site_ip) AS ipaddresses, 
    data_packages.packagename, 
    data_packages.speedlimit, 
    data_packages.threshold, 
    data_packages.accountingdays, 
    data_packages.throttlelimit, 
    data_packages.datalimitamount 
FROM 
    clients 
    JOIN client_site_ip ON clients.userid = client_site_ip.userid 
    JOIN data_packages ON clients.datapackage = data_packages.package_id 
GROUP BY 
    clients.userid

I also need my synchronization query updated, the current script looks like:

SELECT ip_address
              ,SUM(upload_bytes) as upload_bytes
              ,SUM(download_bytes) as download_bytes
              ,sum(upload_bytes + download_bytes) as totalbytes
              ,package_id
              ,username
              ,userid
              ,networkaccess
              ,packagename
              ,speedlimit
              ,threshold
              ,throttlelimit
              ,extendeddata
              ,datalimitamount
              ,accountingdays
        FROM (
              (SELECT ip_accounting.src_address as ip_address
                     ,SUM(ip_accounting.bytes) AS upload_bytes
                     ,0 as download_bytes
                     ,clients.username
                     ,clients.userid
                     ,clients.networkaccess
                     ,clients.extendeddata
                     ,data_packages.package_id
                     ,data_packages.packagename
                     ,data_packages.speedlimit
                     ,data_packages.threshold
                     ,data_packages.throttlelimit
                     ,data_packages.datalimitamount
                     ,data_packages.accountingdays
              FROM ip_accounting
              join clients on clients.ipaddress = ip_accounting.src_address
              join data_packages on data_packages.package_id = 
clients.datapackage
              WHERE dst_address NOT BETWEEN INET_NTOA('192.168.0.1') AND 
INET_NTOA('192.168.255.254')
              and timeanddate BETWEEN SUBDATE(CURRENT_TIMESTAMP(), INTERVAL 
data_packages.accountingdays DAY) AND CURRENT_TIMESTAMP()
              GROUP BY src_address)

              UNION ALL

              (SELECT ip_accounting.dst_address as ip_address
                     ,0 AS upload_bytes
                     ,SUM(ip_accounting.bytes) as download_bytes
                     ,clients.username
                     ,clients.userid
                     ,clients.networkaccess
                     ,clients.extendeddata
                     ,data_packages.package_id
                     ,data_packages.packagename
                     ,data_packages.speedlimit
                     ,data_packages.threshold
                     ,data_packages.throttlelimit
                     ,data_packages.datalimitamount
                     ,data_packages.accountingdays
               FROM ip_accounting
               join clients on clients.ipaddress = ip_accounting.dst_address
               join data_packages on data_packages.package_id = clients.datapackage
               WHERE src_address NOT BETWEEN INET_NTOA('192.168.0.1') AND INET_NTOA('192.168.255.254')
               and timeanddate BETWEEN SUBDATE(CURRENT_TIMESTAMP(), INTERVAL data_packages.accountingdays DAY) AND CURRENT_TIMESTAMP()
               GROUP BY dst_address)
                ) a
        GROUP BY ip_address
        ORDER BY INET_ATON(ip_address)

score 1 · Accepted Answer · answered Dec 06 '17 at 08:16

If you want separate rows for each site_ip and site_name, you can group on these columns as well

SELECT 
    clients.userid, 
    clients.username, 
    clients.datapackage, 
    client_site_ip.userid, 
    client_site_ip.site_name AS sitenames, 
    client_site_ip.site_ip AS ipaddresses, 
    data_packages.packagename, 
    data_packages.speedlimit, 
    data_packages.threshold, 
    data_packages.accountingdays, 
    data_packages.throttlelimit, 
    data_packages.datalimitamount 
FROM 
    clients 
    JOIN client_site_ip ON clients.userid = client_site_ip.userid 
    JOIN data_packages ON clients.datapackage = data_packages.package_id 
GROUP BY 
    clients.userid, client_site_ip.site_name, client_site_ip.site_ip

As far your synchronization query goes, I don't believe you'll need any update since all joins are already on ipaddress etc. and not on group_concat

By synchronization, I am talking about a complete different script, it connects to my router and updates queues and address lists, that is the script that account the traffic from the database, have a look at the last query in my question on how it works. — Raymond Clayton Rudman, Dec 06 '17 at 15:12

Join multiple rows of data to query

1 Answers1

Linked