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

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.

Also, it could be a good idea to post an example of the results you would want your query to return.

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

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.

“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(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.

…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

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';

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.

I will check.
thank for your help.
I really appreciate sharing your knowledge and time.
I will let you know.

thanks
this isexactly what i want.
thank you