SQL command question

55 pts.
Tags:
SQL commands
SQL Server
SQL Server tables
SQL tables
I have three tables as below: Table 1: attributes fields: attID, att_CategoryID, name Table 2: att_Category fields: att_CategoryID, name Table 3: prodcutAttributes fields: ID, productID, attID, I desigining this tables to be able to add multiple prodcut attributes for ecommerce site. I don't know how good this design is. Anyways I need to find out the sql command which can first see how many type of options are entered for the particular prodcut based on that, i need to get each product options entered for the prodcut. example: prodcut 1 might have att_category (COLOR): attributes( blue, black, green) and att_category(Size): attributes (xxl, xl, large) prodcut 2 might have only att_category(SIZE): attributes(large, medium, small) I would appreciate any help on this.

Software/Hardware used:
SQL SERVER

Answer Wiki

Thanks. We'll let you know when a new response is added.

Look up the JOIN syntax. That allows you to join multiple tables together to find out stuff like this.

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ktoby
    i did, but could not find the excat thing i want, thats why i am trying to get help here. just the join is not going to work
    55 pointsBadges:
    report
  • Mariodlg
    I think your desing is not the best one. In the productAttributes table you have the products and its relationships with attributes, but not categories. This means the product's attributes belongs to a single category, and it is not possible to have a general attribute in more than one category. Please bring us more information about the data you will store in those tables, maybe we can suggest you a better desing and after that, think about the appropiate sql command.
    2,790 pointsBadges:
    report
  • carlosdl
    Also, it could be a good idea to post an example of the results you would want your query to return.
    69,510 pointsBadges:
    report
  • TomLiotta
    i did, but could not find the excat thing i want... You will essentially never find the "exact" thing you want. No body else has your tables. Nobody else will ever create that query. That's why database staff is hired. just the join is not going to work The JOIN is exactly what you need. Your example can be shown better in a form like this:
    Table 1: attributes
    fields:
     attID,
     att_CategoryID,
     name 
    
    Table 2: att_Category
     fields:
     att_CategoryID,
     name 
    
    Table 3: prodcutAttributes
    fields:
     ID,
     productID,
     attID,
    Example:
    prodcut 1 might have
    
     att_category (COLOR):
       attributes( blue, black, green)
     att_category(Size):
       attributes (xxl, xl, large)
    
    prodcut 2 might have only
    
     att_category(SIZE):
       attributes(large, medium, small)
    That's a copy of what you posted with only changes in format. But I'm pretty sure almost anyone with a SQL or similar background could generate the results in a minute or so -- using JOIN. Tom
    125,585 pointsBadges:
    report
  • Ktoby
    thank you for your inputs. I think i am here because i want to find some sort of help for my problem. I have posted the structure of the table and the result set i want to generate. it might not have formatted in the correct line tho. This is exactly i posted Table 1: attributes fields: attID, att_CategoryID, name Table 2: att_Category fields: att_CategoryID, name Table 3: prodcutAttributes fields: ID, productID, attID, Example: prodcut 1 might have att_category (COLOR): attributes( blue, black, green) att_category(Size): attributes (xxl, xl, large) prodcut 2 might have only att_category(SIZE): attributes(large, medium, small) Thank for the help. I here thinking because i would learn and for these purpose this forum exist. If you guys are talking about hiring db expert and all please do not even reply. I am posting this to learn and let others learn from my post too. Instead of asking me to hire or something, please try to input your idea to the problem if not others will.
    55 pointsBadges:
    report
  • carlosdl
    "But I’m pretty sure almost anyone with a SQL or similar background could generate the results in a minute or so — using JOIN." It wasn't clear what the OP wanted as result (and I'm still unsure), and not every result could be generated just with JOIN. "I have posted the structure of the table and the result set i want to generate" So, when running the query, this is exactly what you would want it to return ?: "prodcut 1 might have att_category (COLOR): attributes( blue, black, green) att_category(Size): attributes (xxl, xl, large) prodcut 2 might have only att_category(SIZE): attributes(large, medium, small)" Please, do not translate the results to a more human readable representation of it, if this is not exactly what you want as result from the query (if this is really what you want as result, then a function is probably going to be needed), please let us know.
    69,510 pointsBadges:
    report
  • TomLiotta
    ...not every result could be generated just with JOIN. That's true. You could write a function (or procedure) to do the 'join' processing logic, though that wouldn't make a lot of sense (to me). (if this is really what you want as result, then a function is probably going to be needed) That part is definitely true. An actual result with parts that really look like attributes(large, medium, small) is likely to need to be done in a recursive query that would be run over the basic JOIN (or some function that performs 'join' logic) or in a procedure that implements the logic of a recursive query. General knowledge of common table expressions, or CTEs, and recursive queries ( see WITH common_table_expression and Recursive Queries Using Common Table Expressions) might be helpful. With those, values presented through a JOIN could be collected and concatenated into strings (see Concatenate Field Values in One String Using CTE in SQL Server for example). Tom
    125,585 pointsBadges:
    report
  • Kccrosser
    If this is for an e-commerce site, there are some likely problems with the current schema. Many items may have a COLOR attribute, but the specific list of available colors for each item could be different. Similarly, many items may come in different SIZEs, but the available selection of those sizes are likely to differ by item. Further complicating things (thinking ahead) is that if you need to deal with inventory, you may have different quantities of items by specific combinations of attributes. E.g., you might have: 15 Brown, Medium Sweaters made by "Cool Stuff, Inc." 3 Brown, XXL Sweaters made by "Cool Stuff, Inc." 5 Green, Small Sweaters made by "Hot Stuff, Ltd." You need to think about the overall set of items and how people will navigate the site (try going to an established site for ideas). Will they navigate by item category (clothing, furniture, ...), by manufacturer, by item number, by price, by ??? Will you handle back-orders for items with zero quantity on hand? (That actually simplifies your inventory handling in one respect, but then you need some inventory tracking systems.) Pricing and item costs need to be considered as well - will you handle taxable items? Will you handle the different taxable rates by State? Do you need to track which items are subject to taxes vs non-taxable items? Do you need to track item costs over time and handle your inventory against item costs? Once you answer these questions, you can start to design your data model - many of the decisions above will affect the model. A very simplistic model might be: Items table, containing the following fields: ItemID (internal id - primary key) ItemNumber (text? human-readable item number - "X1234") ManufacturerID (foreign key to Manufacturers table) ItemDescription (text) ColorID (foreign key to Colors table) SizeID (foreign key to Sizes table) Colors table, containing fields: ColorID (internal id - primary key) ColorName (text - "Red", "Blue", etc.) Sizes table, containing fields: SizeID (internal id - primary key) SizeName (text - "Small", "Medium", "XL", ...) Manufacturers table, containing fields: ManufacturerID (internal id - primary key) ...Name ...Address ...etc So, to get a record for a given ItemNumber, the query might be:
    select
       It.ItemNumber,
       It.ItemDescription,
       Mf.ManufacturerName,
       Co.ColorName,
       Sz.SizeName
    from
       Items It
          join Manufacturers Mf on Mf.ManufacturerID = It.ManufacturerID
          join Colors Co on co.ColorID = It.ColorID
          join Sizes Sz on Sz.SizeID = It.SizeID
    where
        It.ItemNumber = 'X1234';
    3,830 pointsBadges:
    report
  • Ktoby
    hi Kccrosser Thank you for sharing your knowledge. This is exactly what i am looking to implement. These are the following things i am looking for: User can input as many attribute of the item. It is not just color or size: Let me re-write the structure of my tables. Table 1: Name: attribute Fields: attributeID, attribute_categoryID, name Data: Record 1: attributeID=1, attribute_cateogryID = 1, name = Black Record 2: attributeID=2, attribute_cateogryID = 1, name = White Record 3: attributeID=3, attribute_cateogryID = 2, name = Large Record 4: attributeID=4, attribute_cateogryID = 2, name = Small will make a form where user can enter more attribute records Table 2: Name: attribute_category Fields: attribute_categoryID, attribute_category_name Data: Record 1: attribute_categoryID = 1, attribute_category_name=Color Record 2: attribute_categoryID = 2, attribute_category_name=Size will make a form where user can enter more category. Table 3: Name:product_attribute fields: id, productID, attributeID Data: Record 1: id=1, productID=p1, attributeID=1 Record 2: id=2, productID=p1, attributeID=2 Record 3: id=3, productID=p1, attributeID=3 Based on this table, i need to find how many attributes are available for particular product. Example: select particular productID from Table 3 and check how many attribute are available for this product, based on this result check which category is present and list out all the attribute in the table 3 under each category available for this product. Hope this clears what i want. thanks again.
    55 pointsBadges:
    report
  • Kccrosser
    This is a pretty straightforward query:
    select 
       AC.attribute_category_name,
       ATT.name
    from
       product_attribute PA
       JOIN attribute ATT on ATT.attributeID = PA.AttributeID
       JOIN attribute_category AC on AC.categoryID = ATT.categoryID
    where
       PA.productID = [id to find]
    order by
       AC.attribute_category_name, ATT.name;
    3,830 pointsBadges:
    report
  • Ktoby
    I will check. thank for your help. I really appreciate sharing your knowledge and time. I will let you know.
    55 pointsBadges:
    report
  • Ktoby
    [...] SQL command question [...]
    0 pointsBadges:
    report
  • Ktoby
    thanks this isexactly what i want. thank you
    55 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following