Databases
StackOverflow2013
Postsdbo
POSQL SPROC returning some duplicate records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL SPROC returning some duplicate records
    primarykey
    data
    text
    <p>I thought this was causing a Cartesian effect but the results always return exactly two identical products in the data that are returned. </p> <p>How I can force a distinct on the select? I tried modifying the final select statement to include a "Select Distinct Top..." but it gave me an error regarding syntax. Sorry if this is a simple question, my SQL skills are lacking.</p> <pre><code>( @ProductSKUs varchar(500), @CategoryIDs varchar(40), @RecordCount int ) AS BEGIN declare @Products table (ProductSKU varchar(8), ProductID int not null, TrackInventoryBySizeAndColor int not null) declare @TempCID table(CategoryID int not null default 0) declare @CountCID int set @CountCID=0 declare @productIDcount int select @productIDcount = count(*) from dbo.Split(@ProductSKUs, ',') declare @categoryIDcount int select @categoryIDcount = @RecordCount - @productIDcount -- product SKUs first insert into @Products(ProductSKU, ProductID, TrackInventoryBySizeAndColor) select p.SKU, p.ProductID, p.TrackInventoryBySizeAndColor from dbo.Product p with (nolock) join dbo.Split(@ProductSKUs, ',') pi on p.SKU = pi.items -- now variant SKUs insert into @Products(ProductSKU, ProductID, TrackInventoryBySizeAndColor) select pv.SKUSuffix, pv.ProductID, ISNULL(pv.TrackInventoryBySizeAndColor, 0) from dbo.ProductVariant pv with (nolock) join dbo.Split(@ProductSKUs, ',') pi on pv.SKUSuffix = pi.items --debug --SELECT * FROM @Products if @categoryIDcount &gt; 0 begin insert into @TempCID(CategoryID) select c.CategoryID from Category c with(nolock) --get the subcats join dbo.Split(@CategoryIDs, ',') ci on c.ParentCategoryID = cast(ci.items as int) union select c2.CategoryID from Category c2 with(nolock) --get the category itself join dbo.Split(@CategoryIDs, ',') ci on c2.CategoryID = cast(ci.items as int) insert into @TempCID(CategoryID) select c.CategoryID from Category c with(nolock) join @TempCID tc on c.ParentCategoryID = tc.CategoryID --get level 2 subcats set @CountCID = @@ROWCOUNT insert into @Products(ProductSKU, ProductID, TrackInventoryBySizeAndColor) select TOP (@categoryIDcount) p.SKU, p.ProductID, p.TrackInventoryBySizeAndColor from dbo.Product p with (nolock) left join dbo.ProductCategory pc with (nolock) on p.ProductID = pc.ProductID where pc.CategoryID in (select tc.CategoryID from @TempCID tc) AND pc.ProductID Not in (SELECT ProductID FROM @Products) ORDER BY CHECKSUM(NEWID()) end --debug --SELECT * FROM @Products select top(@RecordCount) p.ProductID, p.Name, pv.VariantID, pv.Name as VariantName, p.ProductGUID, p.Summary, p.Description, p.SEKeywords, p.SEDescription, p.SpecTitle, p.MiscText, p.SwatchImageMap, p.IsFeaturedTeaser, p.FroogleDescription, p.SETitle, p.SENoScript, p.SEAltText, p.SizeOptionPrompt, p.ColorOptionPrompt, p.TextOptionPrompt, p.ProductTypeID, p.TaxClassID, p.SKU, p.ManufacturerPartNumber, p.SalesPromptID, p.SpecCall, p.SpecsInline, p.IsFeatured, p.XmlPackage, p.ColWidth, p.Published, p.RequiresRegistration, p.Looks, p.Notes, p.QuantityDiscountID, p.RelatedProducts, p.UpsellProducts, p.UpsellProductDiscountPercentage, p.RelatedDocuments, p.TrackInventoryBySizeAndColor, p.TrackInventoryBySize, p.TrackInventoryByColor, p.IsAKit, p.ShowInProductBrowser, p.IsAPack, p.PackSize, p.ShowBuyButton, p.RequiresProducts, p.HidePriceUntilCart, p.IsCalltoOrder, p.ExcludeFromPriceFeeds, p.RequiresTextOption, p.TextOptionMaxLength, p.SEName, p.Deleted, p.CreatedOn, p.ImageFileNameOverride, pv.VariantGUID, pv.Description as VariantDescription, pv.SEKeywords as VariantSEKeywords, pv.SEDescription as VariantSEDescription, pv.Colors, pv.ColorSKUModifiers, pv.Sizes, pv.SizeSKUModifiers, pv.FroogleDescription as VariantFroogleDescription, pv.SKUSuffix, pv.ManufacturerPartNumber as VariantManufacturerPartNumber, pv.Price, pv.CustomerEntersPrice, pv.CustomerEntersPricePrompt, isnull(pv.SalePrice, 0) SalePrice, cast(isnull(pv.Weight,0) as decimal(10,1)) Weight, pv.MSRP, pv.Cost, isnull(pv.Points,0) Points, pv.Dimensions, pv.DisplayOrder as VariantDisplayOrder, pv.Notes as VariantNotes, pv.IsTaxable, pv.IsShipSeparately, pv.FreeShipping, pv.IsDownload, pv.DownloadLocation, pv.Published as VariantPublished, pv.IsSecureAttachment, pv.IsRecurring, pv.RecurringInterval, pv.RecurringIntervalType, pv.SubscriptionInterval, pv.SEName as VariantSEName, pv.RestrictedQuantities, pv.MinimumQuantity, pv.Deleted as VariantDeleted, pv.CreatedOn as VariantCreatedOn, d.Name as DistributorName, d.DistributorID, d.SEName as DistributorSEName, m.ManufacturerID, m.Name as ManufacturerName, m.SEName as ManufacturerSEName, s.Name as SalesPromptName from dbo.Product p with (nolock) left join dbo.ProductVariant pv with (nolock) on p.ProductID = pv.ProductID join @Products pid on p.SKU = pid.ProductSKU OR pv.SKUSuffix = pid.ProductSKU left join dbo.SalesPrompt s with (nolock) on p.SalesPromptID = s.SalesPromptID left join dbo.ProductManufacturer pm with (nolock) on p.ProductID = pm.ProductID left join dbo.Manufacturer m with (nolock) on pm.ManufacturerID = m.ManufacturerID left join dbo.ProductDistributor pd with (nolock) on p.ProductID = pd.ProductID left join dbo.Distributor d with (nolock) on pd.DistributorID = d.DistributorID where p.Deleted = 0 and p.Published = 1 ORDER BY p.ShowBuyButton desc </code></pre> <p>EDIT: The @RecordCount is the number of records to return in the final select statement: select top(@RecordCount) --this is passed into the SPROC.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USchuck
    1. USchuck
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL SPROC returning some duplicate records
      1. This table or related slice is empty.
      1. VTClose
    2. VO
      singulars
      1. POSQL SPROC returning some duplicate records
      1. This table or related slice is empty.
      1. VTClose
    3. VO
      singulars
      1. POSQL SPROC returning some duplicate records
      1. This table or related slice is empty.
      1. VTDownMod
    1. COare you adding distinct before or after top(@RecordCount)?
      singulars
      1. POSQL SPROC returning some duplicate records
      1. USadam2510
    2. COI tried adding it before the top(@RecordCount), I see based on Misnomer's comment that that wasn't the way to go about it.
      singulars
      1. POSQL SPROC returning some duplicate records
      1. USchuck
 

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