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?

Answer 1

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});

Answer 2

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}
             );