Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables.
SELECT products.UPC, T2.Price1, vProducts_PriceQty_Union.Price2, vProducts_PriceQty_Union.DBID AS PricingDBID, T2.VendorCount FROM vProducts_PriceQty_Union INNER JOIN products ON vProducts_PriceQty_Union.UPC = products.UPC INNER JOIN ( SELECT UPC, MIN(Price1) AS Price1, SUM(Quantity) AS Quantity, COUNT(DBID) AS VendorCount FROM vProducts_PriceQty_Union GROUP BY UPC ) AS T2 ON T2.UPC = vProducts_PriceQty_Union.UPC AND vProducts_PriceQty_Union.Price1 = T2.Price1 WHERE vProducts_PriceQty_Union.UPC = '082442818986' ORDER BY products.UPC
"vProducts_PriceQty_Union" is a view with a UNION ALL of three tables, which looks like this:
SELECT UPC, Price1, Price2, Quantity, DBID FROM Table1 UNION ALL SELECT UPC, Price1, Price2, Quantity, DBID FROM Table2 UNION ALL SELECT UPC, Price1, Price2, Quantity, DBID FROM Table3
- I realize column name "DBID" is a reserved name, but it's too late in the game to change, and does not seem to affect any query results in an adverse way
- DBID is simply a numeric reference to a table containing the data.
- There are no duplicates of UPCs within any of the tables referenced in the union query, only duplicate UPCs when they are unioned
- The WHERE clause is only shown in the first query to return example results and not used in the production query
Normally, the first query shown above returns one row for each UPC, showing the lowest Price1 from the three available. However, in cases where Price1 is the same amongst more than one table, the query returns more than one row for a given UPC, as follows:
UPC Price1 Price2 Quantity Pricing DBID VendorCount 082442818986 597.0000 569.0000 103.00 1 2 082442818986 597.0000 569.0000 103.00 2 2
My question is:
How do I list only one row for this type of instance (more than one returned), with a preference of using PricingDBID = 1 (the main store, not an offsite warehouse), when one of the results contains PricingDBID = 1. If one of the records does NOT contain PricingDBID = 1, then select only the first one (or random, if you feel up to it) that is returned (all PricingDBIDs above 1 are offsite warehouses and we don't care which one gets chosen, just choose ONE).
I've tried this with CASE statements, but cannot for the life of me figure it out. I suspect it has something to do with the "T2.UPC = Products_PriceQty_Union.UPC AND vProducts_PriceQty_Union.Price1 = T2.Price1" bindings, but not exactly sure what the syntax might be to accomplish this. Over 20 hours on this and no resolution so far, so I'm reaching out to the community. Any help is greatly appreciated!
A follow up question...:
Not sure if this is where this supplemental question should go. If this should be posted as new question, let me know (I'm new to this group as far as posting goes).
When I attempt to paste the working query (see ypercube's answer) into view designer in SSMS and attempt to view the results (ctrl+r), I get "unhandled win32 exception occurred in sqlwb.exe ". Then, when I debug, I'm presented with the dialog "A buffer overrun has occurred in SqlWb.exe which has corrupted the programs's internal state. Press Break to debug". On pressing "Break", the offending line is "0x7813BD93 = 2014559635 and dword ptr ds:[781C4920h],0" (if that means anything to anyone). On stopping code in Visual Studio debugger SSMS closes and I have to start over. I've searched far and wide for a solution to this. However, when I run the query in the query window (not builder), the query runs just fine!
A workaround is to script the creation of the view using Create view, which works just fine for querying. As long as I don't want to edit the query in the view designer, it works just fine. Weird.
Does anyone have any input or experience with this, where queries pasted into view designer cause the program to crash? Ah, Microsoft, it's always something with you...