Problem with IN clause

When you created this table, what did you define as your primary key? If you define it as ProdId and Category, then when you attempt to assign the same category twice for the same item, you won't succeed. That would be good.

Regarding this, "Since I am trying to add products to the tables as quickly as possible, it is far easier and quicker to use the SQL Enterprise Manager than to use a script because some of the fields are the same or close to those from one product to another. "

there is a saying in computer programming, "do it fast, do it twice".

Hi Dan,

Now I am really confused. When I redid my tables to achieve 3NF I created a table with two fields, ProdID and Category. Each product has a unique ID. This number goes in the ProdID field. However, each product may belong to several categories. Therefore, multiple rows in the table will have the same ProdID value but different Category values.

While working in the SQL Enterprise Manager to take the data from my old table and put it in the new table I made an error and used the same Category twice for one of the products. I caught the error when I saw the image for the product appear twice on the website. So, I went back into the Enterprise Manager to change the value of the duplicate to the correct category. Enterprise Manager balked and would not let me make the change. I cannot make either of the fields a key field as I must be able to have a number of identical ProdID entries each with a different Category value but the Category value also is repeated many times for other products. Therefore, I could not assign a key field in the table. This is why I created an ID field as a key field even though I see no use for the data in the field at this time.

What am I missing and how would you address this problem? Since I am trying to add products to the tables as quickly as possible, it is far easier and quicker to use the SQL Enterprise Manager than to use a script because some of the fields are the same or close to those from one product to another. For example, the available colors of the products are sometimes the same as the colors of another product or nearly the same (one or more colors may not be available or are in addition) so I can use a copy and paste and then edit quickly. By the way, the site I am building is at www.recycledpromoproducts.com.

Thanks much. I am certainly willing and eager to learn to do it the right way.

Regards,
Warren

Hi Dan,

It turns out that the problem is documented with Microsoft. The work around is to use the query analyzer rather than to try to change the data using the SQL Enterprise Manager. I was also told that since I have repeating values in both columns of the table (each product can have multiple categories) I should add in a field that I assign as the key field to overcome the problem. I did and that works fine.

I think I have learned a valuable lesson that every table should have an unused key field if there is no other field with unique values.

Thanks for the assistance. I will try to hang around here periodically to both learn and hopefully answer someone else's question.

Regards,
Warren

What's the primary key of this table?

Another option would be an product_price table looking something like this,

product_id pk (fk to product)
min_quantity pk
max_quantity pk
unit_price

This allows you to change the price of one item only.

The sql would resemble

select somefields, unit_price * quantity_ordered as theprice
from product p join product_price pp on p.product_id = pp.product_id
where qty_ordered between min_quantity and max_quantity

If you don't understand the Scarecrow's answer, google "normalized database", or "data modelling tutorial".

You need to redesign your db.

The "category" column should not contain a list.

The reason the query does not work is because you have (if you replace "category" with the example)

AND 2,3,8 IN (1,2,3)

quote:

---

Now I am trying to avoid having to duplicate a row with only the category being different.

---

You need another table, which will hold the record id and the category id.

This is called normalised database.

Ken