SUM values from three tables

Wappler General
,
#1

I have Three tables that are all tied by the key from the first table like this. I have use <-- to signify which field from each table contains the value that ties it to the previous field. I have tried a query with a nest inside of a nest in Server-Connect but I can’t make it work.

Table One.
Jobs:
Job_id <–
Job_name_table
Mob_address
and so on…

Table two:
Job_products_table
job_products_id
job_products_job_id <–
job_products_name
job_products_quantity

Table Three:
Job_shipment_table
job_shipment_id
job_shipment_product_id
job_shipment_job_id <–
job_shipment_tracking_number
job_shipment_quantity

The query I am trying to create will list the job by job_id, Job_products_table where job_products_job_id == jobs_table job_id, and job_shipment where job_shipment_job_id == jobs_table job_id. I need the SUM of the job products table ‘job_products_quantity’ field and the SUM of the job_shipments_quantity field.

The result should be like this:

Job Number: 2323
Total product Quantity: 20
Total products Shipped: 14
then something like;
Remain items to ship: 6

repeat…
repeat…

Any help with this would be greatly appreciated.

#2

I am guessing you have done this as a custom query as you have not formed the query correctly. Thexquery builder will do this for you.
Your query should involve joins to connect all the tables. Something like this, (untested, reply by phone, not near wappler)
I.e select sum(job_priducts_quantity), sum(job_shipments_quantity), from jobs inner join job_products_table on jobs.job_id = job_products_table.job_products_job_id
Inner join job_shipment_table_job_shipment_job_id = jobs.job_id

Always try to use the query builder where possible as it deals with joints etc for you.

#3

I actually have been trying to do it using a repeat in server connect, I will try it your way now.

#4

If you can do it in a query as hyperbites suggests that is your better route to go.

#5

I tried the below and the numbers don’t come our right. It may be helpful to know that the shipping table will not have records for every product because some items were entered before I started tracking the shipments and some jobs don’t have products or shipments. So, the query would have to return zero where there is no matching value. An example would be that I created a job, then add products to that same job, but it has not shipped yet, therefore there is no matching shipping data.

“SELECT SUM(job_products.job_products_quantity), SUM(job_products_shipments.product_shipment_quantity), job_table.job_id FROM job_table INNER JOIN job_products ON (job_products.job_products_job_id = job_table.job_id) INNER JOIN job_products_shipments ON (job_products_shipments.product_shipment_job_id = job_table.job_id) GROUP BY job_table.job_id”

#6

Try changing your inner joins to left joins?

2 Likes
#7

Yes, Brad is right. LEFT JOIN is the way to go.

#8

Do I groupby Job_id, job_products_job_id, or job_shipments_job_id?
I have tried:
Left Left
Right Left
Left Inner
Inner Inner
Right Right
Right Inner
I can’t seem to get the right results. The closest is when I use
Left Inner

#9

Use the LEFT JOIN on the field name shared by both tables, i.e., job_id.

In the other tables that will be joined, add the job_id as a foreign key. Assuming job_id is the ‘root’ table, all the other you want to join, add job_id as an integer type table column.

#11

n query builder I have it like this:
Jobs_Table (Key is Job_ID)
then:
Job_products_table is LEFT JOIN to Jobs_Table (link is job_products_job_id)
Then:
Job_shipping_table is LEFT JOIN to Jobs_Table also(like is job_shipment_job_id)

job_shipments table is also linked to job_products table by Key being Job_products_id and job_shipments_jp_id. I am not using this relationship because I don’t what the shipping results individually I want the Total products ordered and the total shipments entered for the entire job, not product by product. I have already created this result.
I have a dynamic collapse and when it is closed I want the total results so, perhaps we are not working with the same outcome in mind. below is an example.

Job ID: 223
Product: Ordered: Shipped:
cup 2 1
pen 4 2
hat 2 null (not shipped yet)
shirt 4 4

Repeat
repeat another and so on.

Results desired:

Job ID: 223 - Status: Ordered: 12 Shipped: 7 Unshipped: 5
or: SUM(job_products_quantity) AS p, SUM(job_shipments_quantity) AS s, SUM(p-s) WHERE job_products.job_products_job_id = Job_table.job_id AND job_shipment_product_id = job_table_job_id.

I know the syntax is not correct but I hope you can see what I am trying to accomplish.
the job_shipping will not have a record for every job_product_id because the job_shipping table only has tied records after they have been entered. So, if an item is not shipped on a job, like my example, then there will not be a matching record or if a job is entered with products, but none of them have shipped, there will be no matching key in the job_shipment_jb_id, or job_shipment_job_id fields.
I hope i didn’t make this too confusing.

#12

Your query need only to search for the JOB TABLE > job_id. With your joined tables using job_id as a foreign key in your LEFT JOIN tables, the query will find all associated records within each table that equals the job_id. The concept is the relational database use of keys of the root table and foreign keys of other tables.

The unique feature of the LEFT JOIN is that it will include a table though the table columns are empty.

Once you get the query to list the summary data, you should be able to format, or arrange the data to fit whatever you want to display the query results.

When the query is a bit more complex, I find it’s best for me to use a custom query. Here’s an example of one of my custom queries:

SELECT *
FROM pastor
LEFT JOIN church ON pastor.pastor_id = church.pastor_id
LEFT JOIN service ON church.church_id = service.church_id
LEFT JOIN expense ON service.service_id = expense.service_id
WHERE pastor.itinerary_id = :P1
ORDER BY church.service_date, church.am_pm, church.service_time

This query will gather all the data that is associated by each common key from each table. It kind of cascades from the root table toward the last with each sharing a common or related key (ID).

1 Like
#14

At first I thought your suggestion corrected the issue but then I realized that it did not. the problem is that no matter what I try, I can get the sum of the products but the sum of the product shipments is always too low or too high compared to the actual record.
The closest I can get is:
From Jobs
LEFT JOIN job_products On jobs_id = job_products_job_id
LEFT JOIN job_shipments ON jobs_id = job_shipment_job_id
WHERE jobs_id = Pjobs
GROUPBY jobs_id, job_products_job_id, job_shipments_job_id
This give me the right amount of products but double, and sometimes triple the shipment amount.
It almost seems better to use a repeat in the server action to sum the shipments doesn’t it? I’m at a loss here, I have tried everything.

#15

See if this gets you closer…put in a custom query:

SELECT j.job_id, 
(SELECT SUM(jp.job_products_quantity) FROM job_products_table jp WHERE jp.job_products_job_id = j.job_id) total_products_quantity,
(SELECT SUM(js.job_shipment_quantity) FROM job_shipment_table js WHERE js.job_shipment_job_id = j.job_id) total_shipments_quantity
FROM jobs j
1 Like
#17

after doing a little database clean-up, I tried your solution, and it works perfectly, All of the number match the way they are suppose to. I did have to do it in a custom query because I couldn’t figure the steps to do it in Query builder. Thanks much.

1 Like