Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL DISTINCT problem!!

here is my query:
[green]
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"
[/green]

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:
[red]
Microsoft OLE DB Provider for SQL Server error '80040e14'
The text, ntext, or image data type cannot be selected as DISTINCT.
[/red]

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

Comments

  • FlakesFlakes Member Posts: 642
    : here is my query:
    : [green]
    : 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"
    : [/green]
    :
    : 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:
    : [red]
    : Microsoft OLE DB Provider for SQL Server error '80040e14'
    : The text, ntext, or image data type cannot be selected as DISTINCT.
    : [/red]
    :
    : i cant change the 'text' of some columns as those contain html contents. So is there anyway i can make this work?
    :


    Dont know the answer, but it would be better if you posted this in any SQL Server centric messageboards like

    www.sqlteam.com


  • ManningManning Member Posts: 1,621
    : here is my query:
    : [green]
    : 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"
    : [/green]
    :
    : 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:
    : [red]
    : Microsoft OLE DB Provider for SQL Server error '80040e14'
    : The text, ntext, or image data type cannot be selected as DISTINCT.
    : [/red]
    :
    : i cant change the 'text' of some columns as those contain html contents. So is there anyway i can make this work?


    Do you have duplicates in one of the two tables? I don't see why you'd need to use DISTINCT.
  • ioriiori Member Posts: 59
    I have productID as duplicates in tblAccessories so i just want DISTINCT productID from this table and then get the details of this product from tblProducts by matching this productID onto tblProdcuts.
  • ManningManning Member Posts: 1,621
    : I have productID as duplicates in tblAccessories so i just want DISTINCT productID from this table and then get the details of this product from tblProducts by matching this productID onto tblProdcuts.


    I'm still not quite seeing why the DISTINCT is necessary.

    I'm assuming your Products and Accessories table have a one to many relationship (one product can have many accessories). So if you want to get the DISTINCT product ID's from the Accessories table, that would be the exact same thing as retrieving a complete list of product ID's from the products table (well, that might return more results if there are some products which have no accessories).

    So I'm also assuming that what you are trying to do is retrieve a list of products made by a certain manufacturer, as well as any accessories available for that product. If that's the case, then this should work:

    SELECT * FROM tblAccessories A LEFT JOIN tblProducts P ON A.productID = P.productID WHERE P.manufacturerID = the_id

    Assuming you have a product called Product1 and accessories called Accessory1 and Accessory2, you'll end up with two rows looking like:

    [code]
    ProductID, ProductName, AccessoryName
    1 Product1 Accessory1
    1 Product1 Accessory2
    [/code]

    So even if you could use DISTINCT, it wouldn't limit anything because those two rows ARE unique.

    If that's still not what you want, maybe you could explain better the relationship between the two tables, give some example data for each table, and give some example result rows that you want your query to return.
  • ioriiori Member Posts: 59
    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

    [green]tblAccessories(productID,accessoryTypeID,phoneModelSupported)[/green]

    and [green]productID,accessoryTypeID[/green] pair is same and only [green]phoneModelSupported[/green]

    changes: for example:

    [b]productID,accessoryTypeID,phoneModelSupported[/b]
    1,5,5500
    1,5,6800

    so what I want to do is get [b]1,5[/b] one time and match its productID with tblProducts.productID to get

    the details WHERE manufacturerID is 12 for example.

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

    ]

    example:
    [green][b]1[/b],2,[b]12[/b],3340,"my accessory","abc.jpg","def.jpg","my description"[/green]

    and the query i want returned is:
    [green]
    [b]1,5[/b],2,12,3340,"my accessory","abc.jpg","def.jpg","my description"[/green]
  • ManningManning Member Posts: 1,621
    : 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
    :
    : [green]tblAccessories(productID,accessoryTypeID,phoneModelSupported)[/green]
    :
    : and [green]productID,accessoryTypeID[/green] pair is same and only [green]phoneModelSupported[/green]
    :
    : changes: for example:
    :
    : [b]productID,accessoryTypeID,phoneModelSupported[/b]
    : 1,5,5500
    : 1,5,6800
    :
    : so what I want to do is get [b]1,5[/b] one time and match its productID with tblProducts.productID to get
    :
    : the details WHERE manufacturerID is 12 for example.
    :
    : here is schema of tblProducts
    : [green]tblProducts(productID,categoryID,manufacturerID,modelNumber,modelName,smallImg,bigImg,Desc)[/green]
    :
    : example:
    : [green][b]1[/b],2,[b]12[/b],3340,"my accessory","abc.jpg","def.jpg","my description"[/green]
    :
    : and the query i want returned is:
    : [green]
    : [b]1,5[/b],2,12,3340,"my accessory","abc.jpg","def.jpg","my description"[/green]


    Ok, I see the problem now. So I see a couple different options:

    1) My first instinct would be to normalize the tblAccessories table, which would eliminiate the duplicates. Instead of storing the phoneModelSupported information in the tblAccessories table, store it in a new table. This new table would contain your primary key from the tblAccessories table, and the phoneModelSupported column. So much like the product table has a one to many relationship with the accessory table, the accessory table would have a one to many relationship with this new table (ie. one accessory can have many supported phone models).

    Of course, this is best done at the beginning of a project. If you've already got lots of code written, you probably don't want to go changing things.

    2) So the second option, which I'm not sure will work but might be worth trying, would be to switch away from the TEXT columns and use VARCHAR instead. I say this may not work because I'm not 100% sure if VARCHAR will allow DISTINCT to be used, but I think it will. And I've never used TEXT columns before, so I don't know if maybe there's an advantage over VARCHAR which would prohibit you from using it, but if you can switch, it's worth a try. (Just looked, and I see VARCHAR has an 8,000 char limit, so maybe that's why you can't switch? If all your columns have smaller limits than that, then using VARCHAR instead of TEXT should work just the same)

    3) The third option would be to use nested queries. So you'd do:

    SELECT DISTINCT productID, accessoryTypeID FROM tblAccessories

    then loop through that result set, and for each row, do:

    SELECT modelNumber, smallImg, smallDesc FROM tblProducts WHERE productID = current_product_id

    Where current_product_id is the productID for the current row you are working on from the parent query.
  • ioriiori Member Posts: 59
    thanks Manning for the explanation, i think if i go with option#3 it will work , i was trying to do it in one query but thats not possible i think with my database design. If possible can you tell me where i can normalize by table designs.here is my current schema:

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

    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.
  • ManningManning Member Posts: 1,621
    : thanks Manning for the explanation, i think if i go with option#3 it will work , i was trying to do it in one query but thats not possible i think with my database design. If possible can you tell me where i can normalize by table designs.here is my current schema:
    :
    : [green]
    : [b]tblProducts[/b](productID,categoryID,manufacturerID,modelNumber,smallImg,Desc)
    : [b]tblAccessories[/b](productID,accessoryTypeID,phoneModelSupported)
    : [b]tblAccessoryTypes[/b](accesssoryTypeID,accessoryTypeName)
    : [b]tblManufacturers[/b](manufacturerID,manufacturerName)
    : [b]tblCategories[/b](categoryID,categoryName)
    : [/green]
    :
    : 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.


    I think what I mentioned in my previous message is all you really need. But there are other ways to go of course.

    I think I might have split the Products table into separate Phones and Accessories tables. Then you could have a new PhoneAccessories table which would simply contain the PhoneID and AccessoryID. Using this, you'd be able to create a many to many relationship (a phone can have many accessories, an accessory can work with many phones).

    Then when a user clicks on a phone, you could easily present them with a list of all compatable accessories, and possibly all generic accessories (ones not directly tied to any phone, such as headphones or something). And alternatively, when a user clicks on an accessory, you could easily present them with a list of all the phones it will work with (or not show any list, if it's a generic accessory).

    But I don't know the whole story behind your data, so maybe this isn't what you're trying to do, this is just what I'd do based on my understanding of what you've said.
Sign In or Register to comment.