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?
Thanks
David
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
View 8 Replies View RelatedHi,
I have a simple table:
Categories
----------
CategoryID
ParentID
Name
I want to associate my Products to a category so that I can search by category....ok. If I just have a simlpe table:
CategoryProducts
-------------------
CategoryId
ProductId
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?
Thanks,
jr.
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
Hi i've to table where i have a product code and a area code.
I want to insert the values from table 1 into table 2 if the product code and area code do not already exists in the table in table 2.
Am using this code below but it does not seem to be working.
select distinct p_code1, area_code
from table1
where not exists
(select p_code, area_code
from table 2 )
Query that show me a Products Tables and its models, 1 product could have 2 or more models
Example Table Products
id Name
1 Product 1
2 Product 2
3 Product 3
Example Table Models
idmodel idproduct model
1 1 Model 1
2 1 Model 2
3 1 Model 3
4 2 Model 1
5 2 Model 2
6 3 Model 1
And I want to show:
Product Model
Product 1 Model 1
Model 2
Model 3
Product 2 Model 1
Model 2
Product 3 Model 1
How can I query to show that?
Hi
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?
thanks again
Cemal
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"?
SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name
FROM Image, JobTag, Tag, Job, Tagcat
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.
View 1 Replies View RelatedI have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
Hi, all experts here,
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.
With best regards,
Yours sincerely,
I need to capture changes in my table and store it in another table. Here is how my table would look like.
CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
[Code] ....
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.
100 02014-12-20 00:55:44.667
100 12014-12-22 00:55:44.723
100 02014-12-24 00:55:44.723
100 12014-12-26 00:55:44.723
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?
Consider the below tables
Table: Products
----------------------------------------------------
| pid | pname | ranking | cat1 | cat2 | cat3 | mid |
----------------------------------------------------
| 1 | x1 | 20 | 2 | 3 | -1 | 1 |
| 2 | x2 | 40 | 3 | 2 | -1 | 1 |
| 3 | x3 | 80 | 2 | 4 | -1 | 1 |
| 4 | y1 | 40 | 2 | -1 | -1 | 2 |
| 5 | y2 | 60 | 1 | 3 | -1 | 2 |
| 6 | z1 | 50 | 3 | -1 | -1 | 3 |
| 7 | z2 | 70 | 1 | 3 | -1 | 4 |
----------------------------------------------------
Table: Manufacturers
---------------
| mid | mname |
---------------
| 1 | A1 |
| 2 | B2 |
| 3 | C3 |
---------------
Table: Categories
---------------
| cid | cname |
---------------
| 1 | I1 |
| 2 | J2 |
| 3 | K3 |
---------------
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.
Hi guys, I am an absolute beginner to SQL Server, so please excuse my
simple questions. What is the difference between:
1) Microsoft SQL Server 2005
and
2) Microsoft SQL Server 2005 Express Edition
and
3) MSDE?
Do both the SQL Server and Express Edition have the same functionality?
Thanks,
Kon
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?
View 5 Replies View RelatedI 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
Hi everyone,
This is probably a very easy question:
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.
Any ideas?
Thanks heaps,
LJ.
I have successfully downloaded and installed the VS C# 2005 Express, Web Developer 2005 Express, and SQL Server 2005 Express.
View 1 Replies View RelatedHi
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.
Example :-
Product Descripion Catagory Cost
ULTAK TABS 150MG 60 Acid Reducers 384.94
ULTAK TABS 150MG 60 Acid Reducers 85.54
ULTAK TABS 150MG 60 Acid Reducers 56.98
Are there any other products out there to monitor database availability
in SQL server 2000 beside Tivoli by IBM?
Thanks;
Lava
[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!
View 4 Replies View RelatedI 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.
How do I achieve it in SSRS ?
If I do
=Fields!ProductCurrentBalance.Value/SUM(Fields!ProductCurrentBalance.Value, "ds_Top10ProductMix")
Then it divides by the SUM for all products and not the TOP 10 products only.
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:
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'FROM'.
SELECT ampfm.rpt_AdtVisit.PatientFullName, ampfm.rpt_AdtVisit.AdmitPriorityCode, ampfm.dct_AdmitPriorityType.AdmitPriorityTypeName,
ampfm.rpt_AdtVisit.AccountNumber, ampfm.rpt_PatientDemographics.PatientAge, ampfm.rpt_PatientDemographics.PatientAgeGroup,
ampfm.rpt_PatientDemographics.PatientSex,
CASE WHEN PatientAgeGroup = '0 - 14' THEN '0' ELSE
CASE WHEN PatientAgeGroup = '15 - 24' THEN '15' ELSE
CASE WHEN PatientAgeGroup = '25 - 34' THEN '25' ELSE
CASE WHEN PatientAgeGroup = '35 - 44' THEN '35' ELSE
CASE WHEN PatientAgeGroup = '45 - 54' THEN '45' ELSE
CASE WHEN PatientAgeGroup = '55 - 64' THEN '55' ELSE
CASE WHEN PatientAgeGroup = '65 - 74' THEN '65' ELSE
CASE WHEN PatientAgeGroup = '75 - 79' THEN '75' ELSE
CASE WHEN PatientAgeGroup = '80 - OVER' THEN '80'
END END END END END END END END END AS PAGBeginningAge,
CASE WHEN PatientAgeGroup = '0 - 14' THEN '14' ELSE
CASE WHEN PatientAgeGroup = '15 - 24' THEN '24' ELSE
CASE WHEN PatientAgeGroup = '25 - 34' THEN '34' ELSE
CASE WHEN PatientAgeGroup = '35 - 44' THEN '44' ELSE
CASE WHEN PatientAgeGroup = '45 - 54' THEN '54' ELSE
CASE WHEN PatientAgeGroup = '55 - 64' THEN '64' ELSE
CASE WHEN PatientAgeGroup = '65 - 74' THEN '74' ELSE
CASE WHEN PatientAgeGroup = '75 - 79' THEN '79' ELSE
CASE WHEN PatientAgeGroup = '80 - OVER' THEN '200'
END END END END END END END END END AS PAGEndingAge
--WHERE PatientAge NOT BETWEEN PAGBeginningAge AND PAGEndingAge
FROM ampfm.dct_AdmitPriorityType INNER JOIN
ampfm.rpt_AdtVisit ON ampfm.dct_AdmitPriorityType.AdmitPriorityTypeCode = ampfm.rpt_AdtVisit.AdmitPriorityCode INNER JOIN
ampfm.rpt_PatientDemographics ON ampfm.rpt_AdtVisit.RegNum = ampfm.rpt_PatientDemographics.RegNum
i designed a database for eshop.
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
Find out the models and prices for all the products (of any type) produced by maker B.
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
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
i want to make a web page and when somebody come in. i want show for him which products that everyone often buy at that time ( month or summer ).
how i do in data mining to predict that products ?
more: i want know how much percent of product is like by buyer
or i want show products with desc % of the like of people
Greetings
I'm completely stumped by this one.
Yesterday I installed Microsoft Visio Professional 2003 and Microsoft Project 2003.
Today when I open any of my SSIS packages I get this:
There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.
The error list says: Unable to instantiate XML DOM document please verify that the MSXML binaries are properly installed and registered.
When I try to create a new integration services project I get the following modal message box:
Failed to save Package File C:Documents and SettingsMyNameLocalSettingsTemp mp172.tmp with error 0x8002802B - Element Not Found
Yet I can create and run a small C# program no problem, and SQL Server Management Studio seems to be OK.
What do I need to de-install and buy a seperate computer for here? Is it Visio or Project?
Thanks in advance
Hi
I have developed a product
basket mining model as follows
DSV
SELECT C.CustomerId,C.CustomerIdName,P.ProductId,P.ProductIdName
FROM Customer INNER
JOIN CustomerProduct
ON C.OpportunityId
= P.OpportunityId
Mining Structure
CREATE MINING MODEL ProductBasket
(
CustomerId
TEXT KEY,
CustomerIdName
PREDICT,
ProductId
PREDICT,
ProductRecommend
TABLE PREDICT
(
ProductId TEXT KEY
ProductIdName PREDICTONLY
)
)
USING Microsoft_Association_Rules
Prediction Query
Since I want the output in the following format
Product
ID
Items (nested Table)
Product
A
product B
Product C
Product
B
Product A
Product C
I have written the prediction query as follows
SELECT
t.[ProductId],
PredictAssociation([Association].[ ProductId],3)
From
[Association]
PREDICTION JOIN
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT
[ProductId]
FROM
(SELECT ProductId FROM ProductBase)
as [Product]
') AS t
ON
[Association].[Product Id] = t.[ProductId]
The model is predicting the
same set of products for every case. Even changes in the algorithm parameter
value do not have any impact on the result.
What is the reason for this and
how can u rectify it?
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
View 1 Replies View Related