SELECT DISTINCT problem! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT DISTINCT problem!

Author

Topic

iori
Starting Member

20 Posts

Posted - 2005-09-13 : 04:18:51

here is my query:

strSQL="SELECT DISTINCT A.productID,A.accessoryTypeID,P.modelNumber,P.smallImg,P.smallDesc FROM tblAccessories A,tblProducts P " &_
"WHERE P.manufacturerID=" & manufacturer &_
"AND A.productID=P.productID"

here is what i am trying to do:
I want to get DISTINCT productID from tblAccessories and then using those productIDs i want to pull info from tblProducts.
I know that the DISTINCT works on all column names i provided and some of the collumns are of type TEXT therefore i get this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
The text, ntext, or image data type cannot be selected as DISTINCT.

i cant change the 'text' of some columns as those contain html contents. So is there anyway i can make this work?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 04:41:06

you need to exclude those columns from your Select Statement

Try this

Select columns from tblProducts where productID in (select productID from tblAccessories)

Madhivanan

Failing to plan is Planning to fail

iori
Starting Member

20 Posts

Posted - 2005-09-13 : 04:57:08

thanks for the quick response. but how in this case will i get accessoryTypeID from tblAccessories?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 05:03:41

Well. In that case use your original query without Distinct

Madhivanan

Failing to plan is Planning to fail

iori
Starting Member

20 Posts

Posted - 2005-09-13 : 11:56:10

but the problem is tblAccessories has duplicate productIDs and I want to get the DISTINCT productID from that table and then join it with tblProducts based on the productID

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-13 : 12:05:28

So?

And why aren't you doing this inside stored procedures?

Do you have SQL Server client tools installed.

Do you know what Query Analyzer is?

Read the hint link below.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-13 : 12:18:10

quote:
but the problem is tblAccessories has duplicate productIDs and I want to get the DISTINCT productID from that table and then join it with tblProducts based on the productID

quote:
strSQL="SELECT DISTINCT A.productID,A.accessoryTypeID,P.modelNumber,P.smallImg,P.smallDesc FROM tblAccessories A,tblProducts P " &_
"WHERE P.manufacturerID=" & manufacturer &_
"AND A.productID=P.productID"

The problem is if you have doplicate productIDs you may have different accessoryTypeIDs for the same productID, so which one accessoryTypeID do you want to be displayed per distinct productID?

iori
Starting Member

20 Posts

Posted - 2005-09-14 : 12:26:53

Brett, I havent used stored procedures before and not Query Analyzer but i will look into it. thanks
mmarovic, here is the schema of tblAccessories

tblAccessories(productID,accessoryTypeID,phoneModelSupported)

and productID,accessoryTypeID pair is same and only phoneModelSupported changes: for example:

productID,accessoryTypeID,phoneModelSupported
1,5,5500
1,5,6800

so what I want to do is get 1,5 one time and match its productID with tblProducts.productID to get the details WHERE manufacturerID is 12 for example.

here is schema of tblProducts
tblProducts(productID,categoryID,manufacturerID,modelNumber,modelName,smallImg,bigImg,Desc)

example:
1,2,12,3340,"my accessory","abc.jpg","def.jpg","my description"

and the query i want returned is:

1,5,2,12,3340,"my accessory","abc.jpg","def.jpg","my description"

i hope its more clear now what i am trying to do.

thanks

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-14 : 21:04:09

quote:
tblAccessories(productID,accessoryTypeID,phoneModelSupported)

and productID,accessoryTypeID pair is same and only phoneModelSupported changes: for example:

productID,accessoryTypeID,phoneModelSupported
1,5,5500
1,5,6800

so what I want to do is get 1,5 one time and match its productID with tblProducts.productID to get the details WHERE manufacturerID is 12 for example.

here is schema of tblProducts
tblProducts(productID,categoryID,manufacturerID,modelNumber,modelName,smallImg,bigImg,Desc)

example:
1,2,12,3340,"my accessory","abc.jpg","def.jpg","my description"

and the query i want returned is:

1,5,2,12,3340,"my accessory","abc.jpg","def.jpg","my description"

Your db model is not quite normalized, that is the reason for complexity and inefficiency of sql code for a simple problem. Anyway, there are a few solutions for your problem, let's try next one:

declare @manufacturer int
set @manufacturer = <some value>
SELECT P.productID,
       (select top 1 A.accessoryTypeID
           from producttblAccessories A
           where A.ProductId = P.ProductID) as AccessorTypeID,
       P.modelNumber,
       P.smallImg,
       P.smallDesc
FROM tblProducts P
WHERE P.manufacturerID=@manufacturer

Test this script in query analyzer replacing <some value> with existing manufacturerID from tblProducts table. Why do you use hungarian notation for table names?

iori
Starting Member

20 Posts

Posted - 2005-09-15 : 02:30:41

That query didnt return the results i wanted but this one does return:

SELECT productID,modelNumber,modelName,smallImg,smallDesc FROM tblProducts WHERE productID IN(SELECT DISTINCT productID FROM tblAccessories WHERE manufacturerID=10)

but this doesnt give me accessoryTypeID so i am thinking of using another SQL select to query tblAccessories to get accessoryTypeID when looping through the first query.
As you might have gussed i am not very good in databases, i havent been invloved in writing SQL queries alot when coding in ASP and i am using Hungarian notation as im writing my SQL statements in my ASP pages instead of using stored procedures so i have a habit of using table name in the same notations as my variables.
Also i would definitely want to know where my data model is not normalized and what could have been done to avoid this problem;it will definitely help me in the future. thanks once again

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-15 : 03:45:28

Your query is very strange and good example why aliases on column names should be used. Try different manufacturerIDs and see what happens with result set. The query I posted returns exactly the set you defined. If it is not what you wanted give us example where you expected something else then what query is returning.

quote:
and productID,accessoryTypeID pair is same and only phoneModelSupported changes: for example:

productID,accessoryTypeID,phoneModelSupported
1,5,5500
1,5,6800

My understanding is that means that each product is of only accessory type. If that's right you should have separate table with productId (pk), accessoryTypeID and other columns dependant only on productID and you should remove accessoryTypeID from tblAccessories.

iori
Starting Member

20 Posts

Posted - 2005-09-17 : 20:53:29

thanks for the response once again, let me show you the schema of my tables:

tblProducts(productID,categoryID,manufacturerID,modelNumber,smallImg,Desc)
tblAccessories(productID,accessoryTypeID,phoneModelSupported)
tblAccessoryTypes(accesssoryTypeID,accessoryTypeName)
tblManufacturers(manufacturerID,manufacturerName)
tblCategories(categoryID,categoryName)

tblProducts can contain both phones and accessories that are differentiated by categoryID but as 1 accessory can support more than 1 phone i have tblAccessories.
can you tell me where i can normalize it? and are you saying that i put accessoryTypeID in tblProducts and take out accessoryTypeID from tblAccessories?
The reason i dont have accessoryTypeID in tblProducts is not all products entered in tblProducts are accessories.

thanks

what do you think of these changes:

tblProducts(productID,categoryID,accessoryTypeID,manufacturerID,modelNumber,smallImg,Desc)
tblPhonesSupported(productID,phoneModelSupported)

deleted tblAccessories

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-19 : 08:45:46

quote:
tblProducts can contain both phones and accessories that are differentiated by categoryID but as 1 accessory can support more than 1 phone i have tblAccessories.

Does it mean that productID + phoneModelSupported make tblAccessories pk? Does AccessoryTypeID depend on PhoneModelSupported? I guess it depends on ProductID only. If thats true, you have a column in tblAccessories that depends on part of pk. It means it should be placed in table that have ProductID as primary key. If column value is not relevant for each category you can make it nullable. Other solution is to have separate table with just ProductID as pk and AccessoryTypeID. I would do that only if there is really small percent of products that are accessories and there are frequent slow queries accessing that column.

iori
Starting Member

20 Posts

Posted - 2005-09-19 : 19:11:47

yes, I have made it nullable for the entries that are not accessories so it works for me :) thanks mmarovic

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-21 : 12:44:14

quote:
what do you think of these changes:

tblProducts(productID,categoryID,accessoryTypeID,manufacturerID,modelNumber,smallImg,Desc)
tblPhonesSupported(productID,phoneModelSupported)

deleted tblAccessories

quote:
yes, I have made it nullable for the entries that are not accessories so it works for me :) thanks mmarovic

I like your new model better. You are wellcome.

HappyCamper
Starting Member

8 Posts

Posted - 2005-09-21 : 14:39:43

To use Text, nText, etc. columns with "DISTINCT", use convert or cast to temporarily change it's type to a varchar.

Syntax: CAST ( expression AS data_type )

DECLARE @tbl TABLE(ColA Text, ColB int)

SELECT DISTINCT CAST(ColA AS varchar(8000)), ColB
FROM @tbl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources