Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your query will end up with a lot of duplicate results. Consider the following simpler case:</p> <pre><code>Table: Project ProjectID EQuantity 1 1 2 1 Table: Equipment EquipID EPrice 1 1 2 1 Table: Fish FishID 1 2 Table: Project_Equipment ProjectID EquipID 1 1 1 2 2 1 2 2 Table: Project_Fish ProjectID FishID 1 1 1 2 2 1 2 2 </code></pre> <p>Now, let's look at the results of a <code>Project_Equipment</code> query only:</p> <pre><code>SELECT p.projectid, e.eprice, pe.equantity FROM project p INNER JOIN project_equipment pe ON pe.projectid=p.projectid INNER JOIN equipment e ON e.equipid=pe.equipid ProjectID EPrice EQuantity 1 1 1 // a 1 1 1 // b 2 1 1 // c 2 1 1 // d </code></pre> <p>That's as expected; a list of the price and quantity of each piece of equipment used by each project. But what do you think will happen when we <code>INNER JOIN</code> <em>that</em> to <code>project_fish</code>? That first result has project 1 in it twice, and project 2 in it twice, so we end up with every combination of that result and <code>project_fish</code>!</p> <pre><code>SELECT p.projectid, e.eprice, pe.equantity, f.fishid FROM project p INNER JOIN project_equipment pe ON pe.projectid=p.projectid INNER JOIN equipment e ON e.equipid=pe.equipid INNER JOIN project_fish pf ON pf.projectid=p.projectid ProjectID EPrice EQuantity FishID 1 1 1 1 // from a above 1 1 1 2 // from a above 1 1 1 1 // from b above 1 1 1 2 // from b above 2 1 1 1 // from c above 2 1 1 2 // from c above 2 1 1 1 // from d above 2 1 1 2 // from d above </code></pre> <p>This duplication will continue with every inner join. The amount your price will be off won't always be 2x, it will actually depend on the number of combinations of all your joins.</p> <p>So, you can't really do what you are trying to do with this particular query. Instead you'll have to calculate the cost of each relationship separately. Then you sum all those together. You can do this by selecting each one separately and calculating the cost into a <code>ProjectID</code> and <code>ProjectCost</code> column, using <code>UNION</code> to concatenate those altogether, then once again grouping the results by <code>ProjectID</code> and summing the individual <code>ProjectCost</code> subtotals.</p> <p>I explained that poorly, but think of it as subtotaling equipment, fish, and salary costs, then sticking all those subtotals into one table and summing <em>that</em>. E.g.:</p> <pre><code>SELECT x.projectid, SUM(x.ProjectCost) FROM ( SELECT p.projectid, SUM(e.eprice * pe.equantity) ProjectCost FROM project p INNER JOIN project_equipment pe ON pe.projectid=p.projectid INNER JOIN equipment e ON e.equipid=pe.equipid GROUP BY p.projectid UNION SELECT p.projectid, SUM(f.fprice * pf.fquantity) ProjectCost FROM project p INNER JOIN project_fish pf ON pf.projectid=p.projectid INNER JOIN fish f ON f.fishid=pf.fishid GROUP BY p.projectid UNION SELECT p.projectid, SUM(s.salary) ProjectCost FROM project p INNER JOIN project_staff ps ON ps.staffid=p.projectid INNER JOIN staff s ON s.staffid=ps.staffid GROUP BY p.projectid ) x GROUP BY x.projectid </code></pre> <p>Each of the subqueries produces a <code>projectid</code> column and a <code>ProjectCost</code> column. Run the subquery (between the parens) by itself to see the results. The outer query then adds the subtotals for the projects.</p> <p>Sorry, btw, I renamed your EquipPrice and FishPrice columns to EPrice and FPrice when I was testing.</p>
    singulars
    1. This table or related slice is empty.
    1. POJoing multiple with one master table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJason C
    1. USJason C
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POJoing multiple with one master table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI tried your suggested statements !bro @JasonC . But I got this error **#1248 - Every derived table must have its own alias**
      singulars
      1. PO
      1. USzawlatt
    2. COOops; I tested on SQLite where subqueries don't need aliases. Edited, should be fixed now. I aliased the subquery as `x` and added table qualifiers to the outer `projectid` and `projectcost`.
      singulars
      1. PO
      1. USJason C
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload