Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it
When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.
If you need more clarification, post here and let me know.
Hello all I want to create a stroed proceduer with the following:
INSERT INTO Landlord_tbl(col1,col2,col3) SELECT t1.col1, t1.col2 t1.lcol3, FROM import_tbl t1 LEFT OUTER JOIN Landlord_tbl t2 ON t1.potentialSitesID = t2.potentialSitesID HERE t2.potentialSitesID is NULL
Now the aboves works what I want to do if I can is add and update to the full query above without having to write a seprate quyer. Now i know i can run as many queries in a proceduer as I want however for the sake of doing it I want to put in the following:
update t2 set t2.col5 = current_timstamp where t2.col5 is null
I have an application which has a database table called Category. Its very simple, with fields id and categoryname.
My client now wants functionality for sub categories. Whats the best way to do this. My initial thought was to keep the same database table but have a 3rd column called ParentID which contains the ID of the parent category.
Hi, i'm devoloping a web application that contains categories for example Category1 Category11 Category111 Category1111 ........... Category112 Category12 Category2 categories with categories with categories... someone can help me about designing an elegant table for this situation.
Can any body tell me how to add sub-categories to a database (MS SQL Server 2000) please.
I have a db with the following tables set: Products: ProductID, CategoryID, ProductName… Categories: CategoryID, CategoryName …
If a sub-category needs to be added how would I add it and what fields would it contain and the relationship between the Products and Category Table be? Also any useful tips on this would be appreciated.
Is there a correct way to add to the list of Categories shown in the SQL Agent Job properties dialog? I have many jobs on my servers that are [Uncategorized(Local)] as there isnt a Category title that is applicable.
Note: Since Business category is no longer used by any other article then it is also deleted from Categories.
The new Tech category is then created and associated with the updated article as well as the existing category Sports.
Finally I would delete it:
EXEC DeleteArticle @ArticleId = "3"
The tables would become:
Articles Table
ArticleId ArticleText
1 Game ended 1-1
2 Book Review
Categories Table CategoryId CategoryText
1 Sports
2 Entertainment
CategoriesInArticles Table
ArticleId CategoryId
1 1
2 2
Note: Since Tech category is no longer used by any other article then it is also deleted from Categories.
The Sports category is not deleted from Categories since it is used by Article with Id=1
Well, I hope I didn't miss anything and I explained it well.
I have been trying my code, either by creating a separate procedure named SynchronizeCategories or inside the Create, Delete and Update procedure.
Until now I was not able to make this work.
Here is the code I use in my SynchronizeCategories procedure:
INSERT INTO CategoriesInArticles(CategoryId, ArticleId) SELECT c.CategoryId, @ArticleId FROM Categories c INNER JOIN CSVTable(@ArticleCategories) ac ON c.CategoryText = ac.String LEFT JOIN CategoriesInArticles cia ON c.CategoryId= cia.CategoryId WHERE cia.CategoryId IS NULL
INSERT INTO Categories(CategoryId, CategoryText) SELECT cia.CategoryId, NULL FROM CategoriesInArticles cia JOIN [Categories] c ON c.CategoryId = cia.CategoryId INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText WHERE c.CategoryId IS NULL
DELETE c FROM Categories c INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText LEFT JOIN CategoriesInArticles cia ON c.CategoryId = cia.CategoryId WHERE cia.CategoryId IS NULL
I have two tables can I print the Description from Both of them as a single Column from a select statement
Something Like Select Trade_Category.iCategory_Name as Names, FreeCategory.sName as Names From Trade_Category,FreeCategory Where FreeCategory.iParent = 0 OR Trade_Category.iParent_Category = 0
If there's a title that has more than one category (Garages for example), I would like to be able to show both categories in the same row.
Like so:
Title: Firemen Path: firemen.pdf Cat: CGL
Title: Garages Path: garages.pdf Cat: E&O, CGL
Title: Auto Path: autos.pdf Cat: EPLI
I assume that it might be a better set up if the category was a separate table, but I was hoping there is a way around it. I've tried grouping and subqueries but can't seem to get anything to work.
If someone could steer me in the right direction, it would greatly be appreciated. Thanks much and happy Friday!
I would like to know how to get the Top 10 Categories from Reporting Services chart without using the scripting in T-SQL to query from db. Is there a function like Ranking and then we filter the Ranking to be less than or equal 10 ? Please kindly direct me in step by step to the way on how to achieve this as I am quite new in Reporting Services
I am creating a website that has categories such as:Sports -> Soccer -> Soccer ShoesI can not think of an effectively way to implement this into a database. Please help.
Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with
Like
Accommodation (10) Real Estate(30) Automotive(2) Education(1)....
Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by Listing_category.iParentID,trade_category.iCategor y_Name Union ALL Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by Listing_category.iParentID,Freecategory.sName
Which Produces
Real Estate12401 12 Extreme Sports3 4
I would Like to get the same query to produce a list of all the empty records too. so ID Count Accommodation 6112 0 Real Estate 12401 12 retail 12402 0 Extreme Sports3 4 Cycling 5 0
I want your expert opinion to find out the best design solution to this my current issue. 1 . I have a Member Table. These members are sub categorised and again and again. So I have three three category tables which are related and all three tables directly related to the Member table via Foreign keys. IS THIS THE CORRECT DESIGN??
2. I Have a SKILLS table where Members can select their SKILLS from that. I record this in a separate table called "MEMBER SKILLS". There is a SKILL category called "OTHER", then the Member can write those skills which may be not listed in the SKILLS table. I have created a separate column in the "MEMBER SKILLS" table it self to record this free text. IS THIS THE CORRECT DESIGN??
3. How do you resolve a design issue where there are many nested tables of categories and sub categories and the use can create these categories dynamically as well??
I hope all above make sense to you to give a logical answer. Thank you very much for you assistance.
I want to get a list of any Categories where ALL the products in that Category are not published (Published = 0). (I want to get the Categories where no products are listed for it). Here are the tables, not sure where to begin :
SELECT [Id], Published FROM Product WHERE Published = 0
SELECT [Id] ,[Name] FROM Category
SELECT [Id] ,[ProductId] ,[CategoryId] FROM Product_Category_Mapping
Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects: News --With country --With world How i write in Select Command ? SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)
Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects: News --With country --With world How i write in Select Command ? SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)
I am pretty new to the DB part of this but have built an asp.net web appplication with 2 tables: FORMS and UNITS I have created a web page that will allow users to add forms and associate a unit with that form. I now need to be able to allow users to associate the form with multiple units. I can change the web page list box to allow multiple selections but that doesn't solve the problem. This seems like a pretty simple task but I can't seem to find anything on it. any help??? below is the stored procedure I was using: CREATE PROCEDURE dbo.USP_AddForm
I am creating a report that will identify the website categories that our items are in. The purpose is to find items that are not in categories that they should be so they can be fixed. Attached is a csv of a subset of the data.
The data I attached has all of our Baskets by SKU (ItemNoSKU) and the associated web categories that those items are in. For example, we can see that ItemNoSKU AB107 is in web categories 4, 22, and 23.
What I have already done is get a Total Baskets vs Web Cat Baskets. I know the total baskets is 44:
Code: SELECT MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU) FROM myTable GROUP BY MerchCatDesc, MerchSubCatDesc
And I know the number of baskets in WebCat 23 is 43:
Code: SELECT WebCatCd,MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU) FROM myTable GROUP BY WebCatCd, MerchCatDesc, MerchSubCatDesc
So in this instance, I know there is 1 Basket ItemNoSKU that is not in WebCatCd 23. I need to list this "missing" ItemNoSKU along with the WebCatCd it is missing from. I am struggling on how to write the code to accomplish this for my full list of many categories and web categories.
I have about 10 required (never to change) categories in my tbAccountCategories table, but after that, the user can add as many as he/she wishes. I use an identity field to identify each record. The problem is I want to reserve the first 20 rows for the application and thereafter is for the user. Should I just create 20 rows with the category description field = 'Reserved' ?
Also, how do I reindex the identity fields that it cleans up gaps and starts back at 0?
This is what I have currently, still a long way off :/
SELECT TOP (100) PERCENT fcat.Id AS fcat_id, fcat.CategoryName AS fcat_name, fcat.ParentCategory AS fcat_parent, fsub.Id AS fsub_id, fsub.CategoryName AS fsub_name, fsub.ParentCategory AS fsub_parent FROM dbo.ProductCategories AS fcat LEFT OUTER JOIN dbo.ProductCategories AS fsub ON fcat.Id = fsub.ParentCategory ORDER BY fcat_name, fcat_id, fsub_name
I have a simple Stored Procedure with multiple select statements..doing select linkid,linkdesc from links;select cateid,cate from categories... for example. I created a dataset and then fill the tables see the code below ,CS DataSet ds = Obj.GetDataSet("query here); dlc.DataSource = ds.Tables[0]; DataTable llist = ds.Tables[1]; if (llist.Rows.Count > 7) { dr = ds.Tables[1].Rows[7]; dr["cateid"] = 0; dr["cate "] = "more..."; } else { System.Data.DataRow newDataRow = llist.NewRow(); newDataRow["cateid"] =0; newDataRow["cate "] = "more..."; llist.Rows.Add(newDataRow); } lls.DataSource = ds.Tables[1];DataBind(); .ASPX<asp:DataList ID="lls" RepeatColumns="4" runat="server" RepeatDirection="Horizontal" CellPadding="4" CellSpacing="4" BorderWidth="0" Width="100%"> <ItemTemplate> <asp:HyperLink Text='<%# Eval("cate") %>' CssClass='<%# "mcs" + Eval("cate").ToString()%>' NavigateUrl='<%# PickURL(Eval("cateid").ToString(),Eval("cate").ToString()) %>' runat="server" /> </ItemTemplate> <FooterStyle HorizontalAlign="right" /> </asp:DataList> but ITS WORKING FINE SOME TIMES ONLY , FEW TIMES ERROR OCCURRED THE ERROR IS Column 'cate' does not belong to table Table1. I DON'T KNOW WHY THIS ERROR IS COME ANY BODY HAVING SOLUTION TO RESOLVE THIS PROBLEM DETAILED ERROR MESSAGE: Server Error in '/' Application.Column 'lname' does not belong to table Table1.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.ArgumentException: Column 'lname' does not belong to table Table1.Source Error:An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.Stack Trace:[ArgumentException: Column 'lname' does not belong to table Table1.]System.Data.DataRow.GetDataColumn(String columnName) +1887619System.Data.DataRow.set_Item(String columnName, Object value) +14state_common_index.FillIndexPage() +590state_common_index.Page_Load(Object sender, EventArgs e) +1203System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34System.Web.UI.Control.OnLoad(EventArgs e) +99System.Web.UI.Control.LoadRecursive() +47System.Web.UI.Control.LoadRecursive() +131System.Web.UI.Control.LoadRecursive() +131System.Web.UI.Control.LoadRecursive() +131System.Web.UI.Control.LoadRecursive() +131System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
I'm using MS SQL 2000. I developing a shopping cart where on the admin side when we are inserting or updating the products, there is a chance that a single product can fall into two categories therefore i'm giving the users a option of CheckBoxList control for the categories. The checkboxlist control is Database Databinded with categories. When a user selects couple of checkboxes, i'm storing both the categories seperated with a comma in a single field of database but then when i want retrieve the all products of a particular category, this approach wont work. Is their any way around for this?? Even though i use CheckboxList control is their any way to retrieve products of a particular category?? Can i store each checkbox text in a different row with the same product ID's in a different table and all the product info in a different table ?? I hope i made myself clear. Thanks for your help and time in advance.
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?
Hello,I am trying to update variable 'Type' (which is a pre-defined variablewithout any value in it). Is there a way to combine Step1 to Step3 intoone single Update? I tried the query at the bottom but it didn't work.Thanks a lot!--STEP1:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else 'Others'end--STEP2:update TABLE1set type = casewhen name like '%Brokerage%' then 'Brokerage'else 'Others'endwhere type = 'Others'--STEP3:update TABLE1set type = casewhen name like '%card%' then 'Credit Card'else 'Others'endwhere type = 'Others'--Failed query:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else when name like '%Brokerage%' then 'Brokerage'else when name like '%card%' then 'Credit Card'else 'Others'end
HiThanks in advance for any ideas.What I'm trying to do, is have a category system like Yahoo or thesenewsgroups where you have a parent "COMP" and multiple children "LANG"or "DATABASES". However, these can also have children. So for"DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etcMy question is, what is the best way to store these in a database andalso allow me to retrieve the dataI'm not sure how to procedeThanksSam
I have a powerpivot table which groups customers into ranges of sales figures. When I use a slicer to slice by region, I lose some of the ranges because they contain no data. I need them to show up even if there is no data. I've checked all of the boxes to "Show items with no data on columns (and Rows). But this has no effect. I created a measure using the following formula which also has no effect.
I have been asked to derive a bar charts in SSRS. Each bar would represents a different category and each bar should come with different colors. No. of categories might vary every time, it is not fixed. How to achieve this dynamically?
I'm trying to do the equivalent of an Excel chart "Number of categories between tick-mark labels" on the X-Axis of a SSRS chart. Can't see anyway to do it. I can get it to display differently by doing Label = IIF(somethingistrue, onevalue, anothervalue), but can't see anyway to simply not show the label at all.
