Getting data from multiple tables in sql

Question

Table 1: Invoices (inv_id, inv_value, cust_id)
Table 2: Customers (cust_id, sales_rep)
Table 3: Members (Member_id, member_cateogry, member_type, cust_id)

Note 1: Each Customer Pays multiple Invoices. (One-to-Many Relationship).
Note 2: Each Customer pays for one-or-more members (so more than one member could be related to one customer).
Note 3: Each Member has a category which could be 1 "represents Individual" OR 2 "represents Group".
Note 4: Each Member has a type which could be 1 "represents new" OR 2 "represents renew".

I want to get the TOTAL of the Invoice_value field for customers who's sales_rep = 1 and their member_category = 10 and their members_type = 123
Ex: What is the total amount of Invoices that customers paid IF the Sales_rep for these customers was 1 and the members they paid for were new and Individual members.

I tried:

SELECT Sum(invoices.inv_value) AS total
FROM   invoices,
       customers,
       members
WHERE  invoices.cust_id = customers.cust_id
AND    members.custid = customers.cust_id
AND    members.category = {$category}
AND    members_type = {$type}
AND    customers.sales_rep = {$id}";

AND

SELECT     Sum(invoices.inv_value) AS total
FROM       members
INNER JOIN customers
ON         members.custid = customers.cust_id
INNER JOIN invoices
ON         customers.cust_id = invoices.cust_id
WHERE      customers.sales_rep = {$id}
AND        members.category = {$category}
AND        members.type = {$type}";

But both return double the Invoice value.
ex.: 1 Invoice for $120 in the Invoices table return $240 using these sql queries.

How can I fix this?

Add sample table data giving different returned/expected result. (As well formatted text.) — jarlh
– jarlh, Commented Nov 8, 2016 at 11:29
Provide the table schema, relationship between tables, sample data and your expected result — Viki888
– Viki888, Commented Nov 8, 2016 at 11:29
Make sure that there is no duplication in members.category and members.type for the same customer_id — Karim Harazin
– Karim Harazin, Commented Nov 8, 2016 at 11:32
@jarlh The expected result is a "single value" represents the total amount of Invoice values for specific customers. — Mahmoud Emara
– Mahmoud Emara, Commented Nov 8, 2016 at 12:12

Gordon Linoff · Accepted Answer · 2016-11-08 11:30:41Z

1

This is your query:

SELECT sum(i.inv_value) as total
FROM members m INNER JOIN
     customers c
     ON m.custid = c.cust_id INNER JOIN
     invoices i
     ON c.cust_id = i.cust_id
WHERE c.sales_rep = {$id} AND 
      m.category = {$category} AND
      m.type = {$type}";

(Don't use implicit JOIN syntax using commas. It is archaic and less powerful.)

The problem is probably that two members can have the same customer id. You can check this by running:

select m.cust_id, count(*)
from members m
group by m.cust_id
having count(*) > 1;

It is also possible that customer ids are duplicated in customers.

Assuming the duplicates are only in members, change the query to exists:

SELECT sum(i.inv_value) as total
FROM customers c
     ON  INNER JOIN
     invoices i
     ON c.cust_id = i.cust_id
WHERE c.sales_rep = {$id} AND 
      EXISTS (SELECT 1
              FROM members m
              WHERE m.custid = c.cust_id AND
                    m.category = {$category} AND
                    m.type = {$type}
             );

answered Nov 8, 2016 at 11:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Mahmoud Emara Over a year ago

Still returns Double the value :(

Dharman · Accepted Answer · 2020-08-21 19:43:48Z

0

It seems like you are taking inv_value from invoices, which has many to one relationship with customers but customer table and members table have one to many relationship.

Say you have below data

Invoice Table

invoices.cust_id    invoices.inv_value
custid1                 100

Customer table

customer.cust_id
custid1

Members Table

members.cust_id     members.category
custid1                 1
custid1                 2

On join all three tables

customer.cust_id    members.cust_id         invoices.inv_value  members.category
custid1                 custid1                 100                 1
custid1                 custid1                 100                 2

if you notice as custid1 exist in 2 member_category, invoice value is also duplicated.

To solve this, first you can take the distinct records, then summing those distinct records would help you solve your problem as below

Solution

SELECT Sum(invoices.inv_value) AS total
FROM(
    SELECT DISTINCT members.custid, invoices.inv_value inv_value
    FROM  members
    INNER JOIN customers
    ON members.custid = customers.cust_id
    INNER JOIN invoices
    ON customers.cust_id = invoices.cust_id
    WHERE customers.sales_rep = {$id}
    AND members.category = {$category}
    AND members.type = {$type});

edited Aug 21, 2020 at 19:43

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Nov 8, 2016 at 12:28

Viki888

2,7742 gold badges15 silver badges16 bronze badges

3 Comments

Mahmoud Emara Over a year ago

Would you please review your code. I think It makes sense but when I run it I got an error and no value is returned.

Mahmoud Emara Over a year ago

mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given

Viki888 Over a year ago

The error would be due to the way you might have used in your code. Kindly check this

Collectives™ on Stack Overflow

Getting data from multiple tables in sql

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related