May 30, 2012 at 9:35 am
Hi I am trying to create a view to pull all products from dbo.AllSupplierProducts and cross link it with dbo.[files.index] and dbo.manufacturersfromfile to get some further information regarding the products,the table the data is going into cannot accept duplicates (and I dont really want any) so I used select distinct, however this does not appear to be working and I am getting duplicates.
The code is below, can anyone spot any silly mistakes.
SELECT DISTINCT
dbo.[files.index].Product_ID AS Products_id, dbo.AllSupplierProducts.stock AS Products_quantity,
dbo.AllSupplierProducts.ManuPartNo AS Products_model, dbo.[files.index].HighPic AS Products_image,
dbo.[files.index].HighPic AS Products_Image_Med, dbo.[files.index].HighPic AS Products_img_lrg, dbo.AllSupplierProducts.Price AS products_price,
dbo.AllSupplierProducts.Weight AS Products_weight, dbo.manufacturersfromfile.ID AS manufacturers_id,
dbo.[files.index].Product_ID AS icecatcode
FROM dbo.[files.index] INNER JOIN
dbo.manufacturersfromfile ON dbo.[files.index].Supplier_id = dbo.manufacturersfromfile.ID INNER JOIN
dbo.AllSupplierProducts ON dbo.[files.index].Prod_ID = dbo.AllSupplierProducts.ManuPartNo
Thanks for your help.
John.
May 30, 2012 at 9:44 am
Most chances are that you have 2 strings that look the same, but there are some characters that can't be seen. Take for example this code
declare @tbl table (vc varchar(30))
insert into @tbl
select 'demo'
union
select 'demo' + char(10)
union
select 'demo' + char(10) + char(13)
select distinct * from @tbl
select len(vc), vc from @tbl
When I try to find the cause for this type of problem, I start remarking some of the columns in the select list untill I don't get the rows that seem to be duplicated. Then I know what it the problematic column. After that I start looking for the characters that can't be seen (using functions such as len, charindex, substring, etc')
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 30, 2012 at 9:49 am
From your post it is hard to give you an answer. The problem is that we can't sse from here what you see there. Just looking at the code doesn't really help. You need to help us understand the problem. You can do that by reading the first article I reference below in my signature block. Follow the instructions in that article regarding what you need to post and how to post it.
When posting sample data, be sure it is just that, sample data. Also be sure to post the expected results based on the sample data.
I did clean up your code a bit to make it easier for others to look at as well.
SELECT DISTINCT
fi.Product_ID AS Products_id,
asp.stock AS Products_quantity,
asp.ManuPartNo AS Products_model,
fi.HighPic AS Products_image,
fi.HighPic AS Products_Image_Med,
fi.HighPic AS Products_img_lrg,
NULL AS prodimsm1,
NULL AS prodimxl1,
NULL AS prodimsm2,
NULL AS prodimxl2,
NULL AS prodimsm3,
NULL AS prodimxl3,
NULL AS prodimsm4,
NULL AS prodimxl4,
NULL AS prodimsm5,
NULL AS prodimxl5,
NULL AS prodimsm6,
NULL AS prodimxl6,
asp.Price AS products_price,
asp.Weight AS Products_weight,
mff.ID AS manufacturers_id,
fi.Product_ID AS icecatcode,
mff.Image
FROM
dbo.[files.index] fi
INNER JOIN dbo.manufacturersfromfile mff
ON fi.Supplier_id = mff.ID
INNER JOIN dbo.AllSupplierProducts asp
ON fi.Prod_ID = asp.ManuPartNo
May 30, 2012 at 10:06 am
Thanks for you answers, sorry for the messy code...
If I run the view and look at the returned data I can see the duplicates in the product_id field
39853
39853
are both the same however their stock value and price differ, so as Adi Cohn-120898 said their may be hidden characters.
I did expect to return data like this
Products_id Products_quantity Products_model Products Image Etc
12345 6 abc c:\
14321 5 def d:15435 87 ghi g:
As I am using select distinct is this not the best way to disregard duplicates ?
May 30, 2012 at 10:12 am
distinct won't just look at the productID. How can the engine tell which row to discard if it does? Is it the row with the lower price? The higher one?
You are expecting something with distinct you can't get. However, you also haven't defined the problem well. What do you expect from the same product ID, but different prices.
May 30, 2012 at 10:23 am
Sorry Steve that's how I thought the Select Distinct worked ( im very new to this)
I didn't think about it not being able to tell if there were two entries with the same id but different prices what to do with it.
But you saying that has given me the an Idea, I shall remove the higher price item from the table before I perform the view on it.
Thanks for getting me on the right track.
Now how to do this...:)
John.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply