0

My simplified database scheme:

CREATE TABLE `stores` (
  `idStr` int(11) NOT NULL,
  `nameStr` varchar(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `channelProvider` (
  `idCP` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `mount` int(11) NOT NULL,
  `dateCP` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Expenses` (
  `idExp` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `amountExp` int(10) NOT NULL,
  `dateExp` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `products` (
  `idProd` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `nameProd` varchar(255) NOT NULL,
  `priceSale` int(11) NOT NULL,
  `priceBuy` int(11) NOT NULL,
  `qAvailable` int(11) NOT NULL,
  `dateAddProd` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I need to select and get a result as an example:

BENEFIT | Turnover | Total profit of Product | Total profit | Total of Expenses | Total Amount Channel Provider | Month 

I tried with JOINS but I don't get good results. Appreciate any hints!

0

While there isn't enough information to be certain this is your problem, it seems likely since you mention joining many tables.

When you have joins to a number of tables, you can wind up with redundant data in your results. Here's how it works.

Sometimes, a number of joins may be fundamentally linear. For example:

SELECT cust.Name, SUM(prod.Price * oline.Qty) as totalPriceCharged
  FROM Customer cust
         INNER JOIN Order ohead ON (cust.custNo = ohead.custNo)
           INNER JOIN OrderLine line ON (ohead.orderNo = oline.orderNo)
             INNER JOIN Product prod ON (oline.prodID = prod.prodID)
 WHERE ohead.orderDate >= '2018-10-01'
   AND ohead.orderDate <  '2018-11-01'

This query would be intended to show the total price of goods ordered by each customer who placed an order in October of 2018. Each Order ties to a Customer; each OrderLine to an Order; and each Product to an OrderLine entry. Each of the four tables is a part of each row of the output; this is simple, because every row returned from Product is tied to a specific row from OrderLine, and so on. In effect, every table involved has a specific relationship with every other table involved, through that chain of joins:

linear chain of links

However, you also have non-linear joins:

SELECT cust.Name
      ,SUM(empTime.Hours) as totalHrs
      ,SUM(inv.Total) as totalBilled
  FROM Customer cust
         INNER JOIN EmployeeTime empTime ON (cust.custNo = empTime.custNo)
         INNER JOIN Invoice inv ON (cust.custNo = inv.custNo)

Presumably, the goal of this statement would be to see the total amount charged to each customer, versus the total hours employees worked for that customer (yes, we've just changed from provided products to services, apparently).

However, this query won't give the person who wrote it back what they want.

Unlike our previous query, the only connection between EmployeeTime and Invoice is the custNo that links those tables to Customer. Each row returned has to represent one row from Customer, one from EmployeeTime, and one from Invoice. Let's assume our company is just starting out:

Customer:
 custNo |     Name
--------+--------------
    1   | AAA Chemical
    2   | ZZZ Cleaning

EmpTime:
 custNo |  Hours
--------+---------
    1   |   2.0
    1   |   2.5
    2   |   5.9
    2   |   1.1
    2   |   3.0

Invoice:
 custNo |   Total   
--------+-----------
    1   |   100.00
    1   |   125.00
    2   |   295.00
    2   |   205.00

So, the expected results would be:

 custNo |  Hours  |   Total
--------+---------+-----------
    1   |   4.5   |   225.00
    2   |  10.0   |   500.00

What you'd actually get would be:

 custNo |  Hours  |   Total
--------+---------+-----------
    1   |   9.0   |   450.00
    2   |  20.0   |  1500.00

Why?

Again, each row from our result set (before we do our SUMs) has to contain one row from each of the three tables. And, the only way that EmployeeTime and Invoice are connected are through their connection to custNo.

So, how does the database engine know which EmployeeTime rows to connect to which Invoice rows? By the custNo, of course.

Giving us:

 custNo |     Name     |  Hours  |   Total   
--------+--------------+---------+-----------
    1   | AAA Chemical |   2.0   |   100.00
    1   | AAA Chemical |   2.0   |   125.00
    1   | AAA Chemical |   2.5   |   100.00
    1   | AAA Chemical |   2.5   |   125.00
    2   | ZZZ Cleaning |   5.9   |   295.00
    2   | ZZZ Cleaning |   5.9   |   205.00
    2   | ZZZ Cleaning |   1.1   |   295.00
    2   | ZZZ Cleaning |   1.1   |   205.00
    2   | ZZZ Cleaning |   3.0   |   295.00
    2   | ZZZ Cleaning |   3.0   |   205.00

as our underlying data to feed to the SUM functions.

Each row from EmployeeTime for a given Customer has been matched against each row from Invoice for the same customer. This is called a cross-product.

How do you avoid this?

  • You make linear joins, where the relationship between each table is well-defined, and where we don't need cross-products to fill things out (not an option, for this query);
  • You prevent the tables that don't have well-defined connections from "joining" more than one row; or
  • you return a simple set of calculated values, instead of doing joins.

The last two options tend to look very similar; they'll both rely on subqueries to get our values.

Here's our previous query, where we're just joining single rows:

SELECT cust.Name
      ,hours.totalHrs
      ,inv.totalBilled
  FROM Customer cust
         INNER JOIN (SELECT custNo, SUM(Hours)
                       FROM employeeTime
                      GROUP BY custNo
                    ) hours
         INNER JOIN (SELECT custNo, SUM(Total)
                       FROM Invoice
                      GROUP BY custNo
                    ) inv

Technically, this still does a cross-product; however, 1 * 1 = 1

This should work just as well if you're using LEFT OUTER JOIN (or RIGHT...) instead of INNER JOIN.

Or, we just avoid the join altogether, and calculate the totals using suBqueries in the SELECT list:

SELECT cust.Name
      ,(SELECT SUM(Hours) FROM EmployeeTime WHERE custNo = cust.custNo) as totalHrs
      ,(SELECT SUM(Total) FROM Invoice WHERE custNo = cust.custNo) as totalBilled
  FROM Customer cust

Either method should work, if this is indeed the root of your problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.