SQL Add data queried from multiple values queried from one table and multiple columns

Question

I have just added a few more columns (clients.site2_ipaddress clients.site3_ipaddress and so on until clients.site10_ipaddress)

My current query that I have tried to change to add an extra join query:

join clients on clients.site1_ipaddress 
             and clients.site2_ipaddress = ipaccounting.src_address

and

join clients on clients.site1_ipaddress 
             and clients.site2_ipaddress = ipaccounting.dst_address

The output changes everything to site2_ipaddress and only gives one row of data, I need it to add the upload_bytes, download_bytes and total_bytes combined for each of the IP addresses in each columns (site1_ipaddress, site2_ipaddress and so on)

For the life of me I cannot figure out what I am doing wrong...

I also need the output of the rows to add each IP Address with a comma and space, for example: lets say one of my clients have two sites, site1_ipaddress=192.168.1.2 and site2_ipaddress=192.168.1.3

My output needs to be:

ip_address | upload_bytes | download_bytes | total_bytes | .....

192.168.1.2, 192.168.1.3 | upload_bytes? | download_bytes? | total_bytes? | .....

Is that possible?

My complete query with the extra join is:

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
            ipaccounting.src_address AS ip_address,
            SUM(ipaccounting.bytes) AS upload_bytes,
            0 AS download_bytes,
            clients.username,
            clients.userid,
            clients.networkaccess,
            clients.extendeddata,
            datapackages.package_id,
            datapackages.packagename,
            datapackages.speedlimit,
            datapackages.threshold,
            datapackages.throttlelimit,
            datapackages.datalimitamount,
            datapackages.accountingdays
        FROM
            ipaccounting
        JOIN clients ON clients.ipaddress AND clients.`site2_ipaddress` = ipaccounting.src_address
    JOIN datapackages ON datapackages.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 datapackages.accountingdays DAY) AND CURRENT_TIMESTAMP()
        GROUP BY
            src_address)
        UNION ALL
            (
            SELECT
                ipaccounting.dst_address AS ip_address,
                0 AS upload_bytes,
                SUM(ipaccounting.bytes) AS download_bytes,
                clients.username,
                clients.userid,
                clients.networkaccess,
                clients.extendeddata,
                datapackages.package_id,
                datapackages.packagename,
                datapackages.speedlimit,
                datapackages.threshold,
                datapackages.throttlelimit,
                datapackages.datalimitamount,
                datapackages.accountingdays
            FROM
                ipaccounting
            JOIN clients ON clients.ipaddress AND clients.`site2_ipaddress` = ipaccounting.dst_address
            JOIN datapackages ON datapackages.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 datapackages.accountingdays DAY) AND CURRENT_TIMESTAMP()
                GROUP BY
                    dst_address)
                ) a
            GROUP BY
                ip_address
            ORDER BY
                INET_ATON(ip_address)

Clients Table:

IP Accounting Table:

If I wrote a really nice but long solution query, took a screenshot of it, and added that image as an answer, would you be annoyed about the transcription effort? Please don't use images of data, just copy/paste some data into the question and format it using the {} button in the toolbar. — Paul Maxwell
– Paul Maxwell, Commented Nov 8, 2017 at 1:04

Paul Maxwell · Accepted Answer · 2017-12-05 21:42:09Z

1

I regret to say that you have made a bad choice when adding those extra columns. You really needed to create a new table to handle this many-to-one relationship, (many site ip's to one client)

You cannot do this: join clients on clients.site1_ipaddress and clients.site2_ipaddress = ipaccounting.dst_address

You have to do this instead:

join clients on clients.site1_ipaddress  = ipaccounting.dst_address
    OR clients.site2_ipaddress = ipaccounting.dst_address
    OR clients.site3_ipaddress = ipaccounting.dst_address
    OR clients.site4_ipaddress = ipaccounting.dst_address
    OR clients.site5_ipaddress = ipaccounting.dst_address
    OR clients.site6_ipaddress = ipaccounting.dst_address
    OR clients.site7_ipaddress = ipaccounting.dst_address
    OR clients.site8_ipaddress = ipaccounting.dst_address
    OR clients.site9_ipaddress = ipaccounting.dst_address
    OR clients.site10_ipaddress = ipaccounting.dst_address

And for every time you need that "pivoted" ip information spread over 10 columns you are doomed to this type of complexity in every query. Or, create that new table something like this:

client_site_ips
id client_id ipaddress

Then, in our query yo can use GROUP_CONCAT() over the rows in that table to present a comma separated list - if that is what you want.

----

client_site_ip 
siteid, userid, site_name, site_ip

clients id is specified by the userid column

select *
from client_site_ip csi 
join clients c on csi.client_site_ip = c.userid

something like this

edited Dec 5, 2017 at 21:42

answered Nov 8, 2017 at 1:12

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raymond Clayton Rudman Over a year ago

I still do have questions. I have created a new table client_site_ip with the columns named siteid, userid, site_name and site_ip I need to somehow join that to my query to combine accounting data for each ip that belongs to a specific client, the clients id is specified by the userid column. I cannot figure out how to get it right and also keep the query as short as it already takes so much time to execute :(

Paul Maxwell Over a year ago

Raymond, so the question you originally raised is completed, you have in fact accepted the advice given and created the new table. So please "accept" the answer provided.

Paul Maxwell Over a year ago

(nb: I have made a small edit to my answer above) however, for your new problem I suggest you open a new question this will get the attention of many who are wanting to provide answers. please provide whatever query it is you have tried to use, and you will need to supply some sample data from each table (scrub-out anything that might be private when you do that). For performance you will need to provide 1 an explain plan and 2 the DDL of each table including ALL indexes on those tables. I would suggest you hold off asking about performance until you have solved the new joining question.

Collectives™ on Stack Overflow

SQL Add data queried from multiple values queried from one table and multiple columns

1 Answer 1

----

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

----

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related