Multiple records in a select

[rebellad]

I have a table with consisting of products and product id's. I then have a table of attributes consisting of product id,attribute id and attributes. My issue is I want to join the two tables but only return 1 row. I.e if I have a product description of a hammer with a product id of 1. In the attributes table I have two fields with a product id of 1 and attribute 1 = black, attribute 2 = steel. What I want returned is one line :
Product,ProductID,ProductID,Attribute,Attribute
Hammer,1,1,black,steel

 

[r937]

select products.Product
     , attrone.ProductID
     , attrtwo.ProductID
     , attrone.Attribute
     , attrtwo.Attribute
  from products
inner
  join attributes as attrone
    on attrone.product_id = products.product_id   
inner
  join attributes as attrtwo
    on attrtwo.product_id = products.product_id  
   and attrtwo.attribute_id <> attrone.attribute_id

r937.com | rudy.ca

 

[rebellad]

Thanks for this, it works perfectly. Only thing is I have come across another problem as there can be anywhere between one to ten attributes for each product.... Any more suggestions??

 

[r937]

use ten joins -- you are denormalizing multiple rows into multiple columns and that's why it's so clunky

and for each join, check each attribute_id to make sure it isn't equal to any of the ones before (so in the tenth join you will have nine <> comparisons)

and make them LEFT OUTER JOINs, not INNER JOINs, to cater for situations where some products might not have all ten attributes

two questions for you:

why do you want to denormalize like that?

what database are you using?

r937.com | rudy.ca

 

[rebellad]

Basically I am using a select statement for an application we have just bought. It's an oracle database. The problem is that all I can use is a select and nothing else, otherwise I could have used some temporary tables and stuff - it's a disaster but your answer has been a great help. Thanks