Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To clarify my query, and somewhat vague description of the table columns of the "buyable" table, The "Parent_ID" is the basket in question. The "Shop_Week_ID" is the consideration for baskets to be compared... don't compare a basket from week 1 to week 2 to week 3. The #ID column appears to be a sequential ID in the table, but not the actual ID of the item to be compared... The Location_ID appears to be the common "Item". In the scenario, assuming a shopping cart, Location_ID = 103 = "Computer", Location_ID = 204 = "Television" (just for my interpretation of the data). If this is incorrect, minor adjustments may be needed, in addition to the original poster showing a list of say... a dozen entries of the data to show proper correlation.</p> <p>So, now, on to my query.. I'm doing a STRAIGHT_JOIN so it joins in the order I've listed. </p> <p>The first query for alias "MainBasket" is exclusively used to query how many items are in the basket in question ONCE, so it doesn't need to be re-joined/queried again for each possible basket to match. There is no "ON" clause as this will be a single record, and thus no Cartesian impact, as I want this COUNT(*) value applied to EVERY record in the final result.</p> <p>The NEXT Query is to find a DISTINCT OTHER Basket where at LEAST ONE "Location_ID" (Item) within the same week as the parent in question... This could result in other baskets having 1, same or more entries than the basket. But if there are 100 baskets, but only 18 have at least 1 entry that matches 1 item in the original basket, you've just significantly cut down the number of baskets to do final compare against (SameWeekSimilar alias result).</p> <p>Finally is a Join to the buyable table again, but based on a join for the SameWeekSimilar, but only on per "other" basket that had a close match... No specific items, just by the basket. The query used to get the SameWeekSimilar already pre-qualified the same week, and at least one matching item from the original basket in question, but specifically excluding the original basket so it doesn't compare to itself.</p> <p>By doing a group at the outer level based on the SameWeekSimilar.NextBasket, we can get the count of actual items for that basket. Since a simple Cartesian join to the MainBasket, we just grab the original count.</p> <p>Finally, the HAVING clause. Since this is applied AFTER the "COUNT(*)", we know how many items were in the "Other" baskets, and how many in the "Main" basket. So, the HAVING clause is only including those where the counts were the same.</p> <p>If you want to test to ensure what I'm describing, run this against your table but DO NOT include the HAVING clause. You'll see which were all the POSSIBLE... Then re-add the HAVING clause and see which ones DO match same count...</p> <pre><code>select STRAIGHT_JOIN SameWeekSimilar.NextBasket, count(*) NextBasketCount, MainBasket.OrigCount from ( select count(*) OrigCount from Buyable B1 where B1.Parent_ID = 7 ) MainBasket JOIN ( select DISTINCT B2.Parent_ID as NextBasket from Buyable B1 JOIN Buyable B2 ON B1.Parent_ID != B2.Parent_ID AND B1.Shop_Week_ID = B2.Shop_Week_ID AND B1.Location_ID = B2.Location_ID where B1.Parent_ID = 7 ) SameWeekSimilar Join Buyable B1 on SameWeekSimilar.NextBasket = B1.Parent_ID group by SameWeekSimilar.NextBasket having MainBasket.OrigCount = NextBasketCount </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSolution for finding "duplicate" records involving STI and parent-child relationship
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDRapp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSolution for finding "duplicate" records involving STI and parent-child relationship
      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. 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