Databases
StackOverflow2013
Postsdbo
POTop N Per Group with Multiple Table Joins
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTop N Per Group with Multiple Table Joins
    primarykey
    data
    text
    <p>Based on my research, this is a very common problem which generally has a fairly simple solution. My task is to alter several queries from <em>get all results</em> into <em>get top 3 per group</em>. At first this was going well and I used several recommendations and answers from this site to achieve this (Most Viewed Products). However, I'm running into difficulty with my last one "Best Selling Products" because of multiple joins. </p> <p>Basically, I need to <strong>get all products in order by # highest sales per product in which the maximum products per vendor is 3</strong> I've got multiple tables being joined to create the original query, and each time I attempt to use the variables to generate rankings it produces invalid results. The following should help better understand the issue (I've removed unnecessary fields for brevity):</p> <p>Product Table</p> <pre><code>productid | vendorid | approved | active | deleted </code></pre> <p>Vendor Table</p> <pre><code>vendorid | approved | active | deleted </code></pre> <p>Order Table</p> <pre><code>orderid | `status` | deleted </code></pre> <p>Order Items Table</p> <pre><code>orderitemid | orderid | productid | price </code></pre> <p>Now, my original query to <em>get all results</em> is as follows:</p> <pre><code>SELECT COUNT(oi.price) AS `NumSales`, p.productid, p.vendorid FROM products p INNER JOIN vendors v ON (p.vendorid = v.vendorid) INNER JOIN orders_items oi ON (p.productid = oi.productid) INNER JOIN orders o ON (oi.orderid = o.orderid) WHERE (p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0) AND (v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0) AND o.`Status` = 'SETTLED' AND o.Deleted = 0 GROUP BY oi.productid ORDER BY COUNT(oi.price) DESC LIMIT 100; </code></pre> <p>Finally, (and here's where I'm stumped), I'm trying to alter the above statement such that I received only the top 3 product (by # sold) per vendor. I'd add what I have so far, but I'm embarrassed to do so and this question is already a wall of text. I've tried variables but keep getting invalid results. Any help would be greatly appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRichardTheKiwi
    1. USJeremy
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTop N Per Group with Multiple Table Joins
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTop N Per Group with Multiple Table Joins
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POTop N Per Group with Multiple Table Joins
      1. USRishiD
      1. VTFavorite
    1. This table or related slice is empty.
 

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