How to join 3 tables and get values from another table sqlite?

Question

1.00/5 (1 vote)

See more:

I have 4 tables in my database SQLITE as following:
1- products
2- customers
3- sales
4- purchases

sales table has product ID (pro_id) and customer ID (cu_id) I am trying to join customers and products in sales using inner join but my problem is I am trying to get values of AVG prices from another table purchases this is my queries:

SELECT sa_id, sa_date, p_name, cu_name, sa_quantity, sa_price, sa_amount FROM sales INNER JOIN products ON products.p_id = sales.pro_id INNER JOIN customers ON customers.cu_id = sales.cu_Id


SELECT Sum(pur_quantity * pur_price) / Sum(pur_quantity) As AVG_Price FROM purchases group by p_id

What I have tried:

I tried this below query:

SELECT sa_id, sa_date, p_name, cu_name, sa_quantity, sa_price, sa_amount (SELECT Sum(pur_quantity * pur_price) / Sum(pur_quantity) As AVG_Price FROM purchases group by pro_id) AVG_Price FROM sales INNER JOIN products ON products.p_id = sales.pro_id INNER JOIN customers ON customers.cu_id = sales.cu_id

Posted 21-May-23 2:16am

brahim farhat (AAD)

Updated 21-May-23 10:40am

v3

Add a Solution

Comments

[no name] 21-May-23 9:53am

Use "intermediate" queries if you can't get your head around doing it in "one statement". It never gets easier.

PIEBALDconsult 21-May-23 17:05pm

OUTER JOIN ?

2 solutions

Solution 1

The relations between tables are not quite clear but are you looking for a correlated subquery? Something like

SQL

SELECT sa_id, 
       sa_date, 
       p_name, 
       cu_name, 
       sa_quantity, 
       sa_price, 
       sa_amount,
       (SELECT Sum(pur.pur_quantity * pur.pur_price) 
               / Sum(pur.pur_quantity)
        FROM purchases pur
       WHERE pur.pro_id = sales.pro_id) AS AVG_Price 
FROM sales sales
INNER JOIN products  ON products.p_id = sales.pro_id 
INNER JOIN customers ON customers.cu_id = sales.cu_id

Posted 21-May-23 4:07am

Wendelius

Comments

brahim farhat (AAD) 21-May-23 16:41pm

There was a typo i updated my question see now?

Wendelius 22-May-23 0:14am

I see that you removed the underscore but what about the actual question. Is the suggested subquery correlation what you're after?

WHERE pur.pro_id = sales.pro_id

brahim farhat (AAD) 23-May-23 21:16pm

Here is my final solution i tried to post the answer but it says

 inmodorate

:

SELECT sa_id, 
       sa_date, 
       p_name, 
       cu_name, 
       sa_quantity, 
       sa_price, 
       sa_amount,
       Sum(pur_quantity * pur_price) 
               / Sum(pur_quantity)
        AS AVG_pur_Price 
FROM sales
JOIN products  ON products.p_id = sales.pro_id 
JOIN customers ON customers.cu_id = sales.cu_id
join purchases on purchases.pro_id=products.p_id
group by sa_id

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

brahim farhat (AAD) · Accepted Answer · 2023-05-23T15:12:00

Solution 2

I solved my problem by this query :

SELECT sa_id, 
       sa_date, 
       p_name, 
       cu_name, 
       sa_quantity, 
       sa_price, 
       sa_amount,
       Sum(pur_quantity * pur_price) 
               / Sum(pur_quantity)
        AS AVG_pur_Price 
FROM sales
JOIN products  ON products.p_id = sales.pro_id 
JOIN customers ON customers.cu_id = sales.cu_id
join purchases on purchases.pro_id=products.p_id
group by sa_id

Posted 23-May-23 15:12pm

brahim farhat (AAD)

Updated 23-May-23 16:02pm

v2

Comments

PIEBALDconsult 23-May-23 22:03pm

Please don't try to answer your own question. Just use the Improve question button to add detail and such.

brahim farhat (AAD) 24-May-23 16:15pm

Ok