How Can I Store More Than One Category In A Products Table?
Apr 16, 2007
How can I store more than one category in a products table?
For exampe: I have a dvd website where the admin can add new dvd's. On the website all the categories are listed with a checkbox. If the dvd is a action comedy the admin have to check these two checkboxes. But how do I store that in the sql database an retrieve it?
selecting number of products that comes under a parent categorysqlserver2005CREATE TABLE [ProductCategoryAssociation]([CategoryID] [bigint] , --this is primary key of table Category[ProductID] [bigint] ,--this is primary key of table product) the above table binds a product to its category CEATE TABLE [Category]([CategoryID] [bigint] IDENTITY(1,1) -- this is primary key of table category[Name] [nvarchar](255)[ParentCategory] [bigint] NULL,) I have a Category"A" as a supper parentcategoryI have 1product in Category"A"Category"A" have 2 child category A11,A12. A11,A12 both have 2 products in the category (totally 4)Now I can get the count of all product belong Category"A" (ie is 5= 1+2*2) fivelet 1 be the id of category "A" Now the following querry give me the expected resultselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1) BUT if i have child for A11 named A111 and if there is ONE product belong to A111 this querry wont take that in to cosiderationso the following querry will do itselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1 or Parentcategory in (select categoryid from Category where Parentcategory=1))ie i nested one more selectbuthis has limitationsSo kindly give me a general solution to check up to the las level of category
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I click on Self Help while browsing, I want to see all items underneath Personal Development, Spiritual and Meditiation. So my question is is this a good way to store the product-category relationships, or should I put many entries into CategoryProducts to keep the queries simlpe and faster? Are they faster doing it this way? In this way there would be 4 entries for a product in meditation. My personal idea is that adding all entries up a tree arm of a category path will be cumbersome to manage, but it does solve the problem of clicking on Self Help and seeing all products that exist within sub-categories. I am sure an SQL query would be able to work this out, but I dont know if performance would be something to consider on an ecommerce site? Are there any patterns fo rthis stuff - seems a reasonably repeatable pattern for business sites?
hello, i have a Products table, and i want to make an image data type to one of his rows (Picture1 for example), i want to know, what is best, to store the picture in the database or store only the direction to the picture? if i store only the direction, i should take it from some output parameters of the Upload function of ASP and the add it to the Database? can i add it to a special folder for example MySiteUserName + file name? and another thing: let's say i have Promotion - tinyint, to store the promotion value of this product..If i show products using DataList, i could order my products first after Promotion and then after date added? could i use a special CssClass(font weight or other background) for the products witch has the Promotion more than 10 for example? how can i know the exact date time (yy/mm/dd/hh/mm) ? - it is taked from the Server date? thank you
I new to sql procedures and I need your advice on below subject. Thanks in advance.
I have products table that has catid (string 50) and subcatid (string 50) I like to transfer the string to another table (categories) and keep only int values in products catid and subcatid.
therefore I created categories table and added 3 fields;
catid int (primary and autonumber) catname string 50 will be equal to category name in products table parentid int (if it is a parent category, it will be equal to 0 otherwise if it is a subcategory it will be equal to parentcatid)
so instead writing a vb program I like to ask your advice on better solutions.
please help
Also I am using sql express 2005 and I wanted to practice (learn) sql DTS. is there any way I can download this package and work it with my system?
We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.
We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?
WHERE Tag.TagID = JobTag.TagID AND Image.JobID = Job.JobID AND Job.JobID = JobTag.JobID AND TagCat.TagCatID = Tag.TagCatID
ORDER BY Image.ImageID, Tag.SortOrder
I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?
I'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...
First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.
So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.
1. Does file groups separated by table type make sense? 2. Should you put tables that are queried often together in the same or different file groups. 3. I'm pretty sure you can't restore single file group for write access, am I correct?
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
For the last 2 years we have been using ArcServe for Windows NT (version 6.0 and 6.5) with the Backup Agent for SQL Server. We are now re-evaluating our backup software. Our primary issues with ArcServe are that it has been unstable, unreliable and it does not support the full SQL Server recovery functionality. ArcServe does not include the ability to do point-in-time database recovery. Nor does it support single table restores (when a full database backup was performed). These last 2 items are critical and necessary features at my installation.
What products do people recommend for performs Windows NT files and MS-SQL Server backups?
Say if I want to get the top 3 products that fall under category "J2" and have them ordered by ranking then I would use the
below query
ELECT DISTINCT TOP 3 mid, pid, pname, prank, mname FROM Products, Manufacturers, Categories WHERE Products.mid = Manufacturers.mid AND (cat1=2 OR cat2=2 OR cat3=2) ORDER BY ranking
Now what if I need the top 3 ranked products that fall under category "J2" and have them ordered by ranking selecting only
one product from each manufacturer. So in order words I want the top ranked product from each manufacturer.
What would that query be?
I am having a really tough time and have already spent quite sometime on it unsuccessfully. I would appreciate any help.
I want to have visitors to my website get price calculations from my database based on input variables which the visitor enters. I think I need the combination of Access, ASP.NET, and Sql Sever. Is this correct?
I am trying to write a bit of code that I can pass a brand name to. If the brand name exists I want to return the brandid to the calling middle tier. If the brand id does not exist I want to insert and then return the new brand id. The code below works unless the brand does not exist. Then it inserts, and I get an application exception. Next time I run the code it continues on until the next time it has to do an insert. So the inserts are working, but getting the value back is resulting in an application excetio.
Middle Tier Function ( private static int GetBrandForProduct(clsProduct o) { int brandid = -1; // If the brand name comes in blank use the first word of the overstock product o.BrandName = o.BrandName.Trim(); // if we do not have a brand for this product if (o.BrandName.Length == 0) return -1; Database db = CommonManager.GetDatabase(); ; try { // Get the brand id for this brand name // If it does not exist we will add it and STILL return a brand id object obj = db.ExecuteScalar("BrandIDGetOrInsert", o.BrandName); string catid = obj.ToString(); *** FAILING LINE *** return Convert.ToInt32(obj.ToString()); } catch (Exception ex) { throw ex; return -1; } return brandid; } Stored Procedure: ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- ALTER PROCEDURE [dbo].[BrandIDGetOrInsert] -- Add the parameters for the stored procedure here @brandnameparm varchar(50) AS BEGIN -- SET NOCOUNT ON SELECT brandid from brands where Lower(brandname) = Lower(@brandnameparm) -- If we found a record, exit
if @@rowcount > 0 return -- We did not find a record, so add a new one.
begin
insert into brands (Brandname) values(@brandnameparm)
end SELECT brandid from brands where Lower(brandname) = Lower(@brandnameparm) END
I have a shopping cart table and two products tables (legacy reasons...).
I need to create a relationship between the ShoppingCart table and the two products tables such that no products are entered in the Cart which don't exist in either product table 1 or product table 2.
I'm trying to select the TOP 20 Products per Catagory out of my Star Topology Database if someone would PLEASE be able to help me out.
Here is my Query: -
SELECT Distinct (SELECT TOP 20 DP.[Description]), DP.Mims_Sub_Cat, SUM(FD.Cost) AS 'Cost' FROM DIM_Product DP, FACT_Dispensary FD, DIM_Time DT, DIM_Client DC WHERE DP.Product_KEY = FD.Product_Key AND FD.Time_KEY = DT.Time_KEY AND FD.Client_Key = DC.Client_KEY AND DT.[Year] = 2007 AND DT.[Month] IN (2) AND Client_name LIKE '%Medicare%' AND DP.Manufacture_Name LIKE '%Cipla%' --AND DP.[Description] IN (SELECT TOP 20 [Description]) AND DP.Mims_Sub_Cat IN (SELECT Mims_Sub_Cat) GROUP BY DP.Mims_Sub_Cat, DP.[Description], FD.Cost ORDER by DP.Mims_Sub_Cat, SUM(FD.Cost) DESC
My other problem is that it keeps on selecting the same products although i have a distinct in my query The query will select a product with the amount and then select the same product again with a different amount.
[crossposted]Hi, I wonder if anyone might lend me a brain.I have a stock database to build that covers over 1000 products, whichmight be said to exist in around 50 product families.Obviously, just to be awkward all the types of stock will havedifferent attributes. So one product might be a tube withinside/outside diameter and length and another a T shaped cable joint.All I can come up with is a separate table for each stock type familyand store the table name and product code in the main stock table, so:Tables:ProdAProdBProdCStockStock attributes:ProdIdProdTableAmountDateetc..ProdA attribute:ProdIdAttributeXAttributeYAttributeZetc..Then use code to parse the table and product ID to select the correctquery to get the product details. BUT This seems awefuly inelegant andpotentially wrong so I'm loathe to continue down this route.Can anyone tell me the "right" way to do this, I feel sure it must bea classic db design exercise, but unfortunatly one they didn't teachus at University -- or maybe I was asleep...Thanks!
I got a stored proc which returns the result dataset.
My requirement is to sort the final output depending on a parameter value (which is dependent on 2 different amount column from the result dataset) , display only top 10 products and equate the % mix for those 10 products only. Basically the % mix column should sum up to 100% for those top 10 products.
The query I have written below works fine. However, I now want to uncomment the WHERE clause below to find entries where the PatientAge does not fall between the PAGBeginningAge and PAGEndingAge. However, when I uncomment the WHERE clause line I receive the following error message:
i have two table one of them is products and it has id,code,name columns and the other oneĀ is option with id,proid,option,optiondes column. and there is a relationship with id from products to proid in option table.
i want to make query that results is compare two or more products with the same option column.
Hello, I'm wondering what would be the best approach to designing a database that will have different products one of them being T-Shirts of different sizes... for example 1 t-shirt design might only have 2 available sizes while another may have 4. I'm kinda stumped on how to approach this cuz there is multiple products like CD's, DVD's, Magazines etc which is pretty straight forward, but the T-shirts have this "variable" to it. What i'm really wondering is should i have 1 main "Products" Table or should i have a separate table for the t-shirts? Should there be a column for each available size? Currently my database has a "products table" that has foreign keys to "Product Type", "Artists", "Genre" The database is basically for a record company If anyone has designed a database similar to this i'd love any insight or even possibly to see a database diagram Thanks
select product.type, pc.price as pcprice, laptop.price lapprice, pc.model as pcmod, laptop.model as lapmod from product join pc on product.model=pc.model join laptop on laptop.model=product.model where maker = 'B'
the syntex runs but its not displaying any results + I know that I have some extra columns there but its for some thing else I was trying
HelloI am using sql server 2005.I have two tables as described below.Table1UserID UserSales---------------------1 102 133 174 195 216 107 128 119 3110 2311 2412 1013 16Table2UserID Country----------------------1 Canada2 Canada3 Canada4 Canada5 Canada6 USA7 USA8 USA9 USA10 USA11 UK12 UK13 UKI want to get top 2 UserSales for each country and remaining should bedisplayed as Total as Others for that country.Can someone please help me with this query?RegardsAmit