Column 'coloumname' Does Not Belong To Table Categories .

Dec 22, 2007

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)
+1887619
System.Data.DataRow.set_Item(String columnName, Object value)
+14
state_common_index.FillIndexPage()
+590
state_common_index.Page_Load(Object sender, EventArgs e)
+1203
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o,
Object t, EventArgs e)
+15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender,
EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e)
+99
System.Web.UI.Control.LoadRecursive()
+47
System.Web.UI.Control.LoadRecursive()
+131
System.Web.UI.Control.LoadRecursive()
+131
System.Web.UI.Control.LoadRecursive()
+131
System.Web.UI.Control.LoadRecursive()
+131
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

View 1 Replies


ADVERTISEMENT

Column 'AnyColumn' Does Not Belong To Table Table

Oct 17, 2007

Does anyone know about this error:
Column 'AnyColumn' does not belong to table Table

View 3 Replies View Related

How To Find All Child Tables That Belong To A Parent Table

Mar 5, 2008

How do i find the child tables that belong to a parent table. Thanks.

View 10 Replies View Related

Categories Table

Jun 10, 2007

hi to all;



i want to make in my site the possibility for the user to make categories in the site



ex: he can add the category name"computer science" and then can add the category childs " data mining, software engineering,... "



so firstly i want to make a category table in my databasde to be able to save the category data

haw can i make this table? i mean what are the columns that should be in the table?what type of relationship should i make? and how to mahe it



sorry but i need any one to explain to me what should i do. and if anyone one know the solution please reply to me



thanks....

View 1 Replies View Related

Join Category Table So That Categories Are In The Result On Only

Feb 22, 2007

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?

View 1 Replies View Related

How To Get All Sub Categories For All Parent Categories

Jan 18, 2012

I have a table Category whose structure is as follows:

id
name
parentid

So i have number of nodes from the root of a specific category. How to get all the sub categories(ie nodes) for all parent categories?

View 14 Replies View Related

Categories And Sub Categories Best Practice

Aug 9, 2007

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.

Is this the best way? Or something else.

Thanks

View 2 Replies View Related

Find Out Next Top 10 That Does Not Belong To First Top 10?

Aug 31, 2014

I have a data of 100 PostalCodes and parcels associated with it. Each postal code will be having multiple parcels. My aim is to find the count of parcels for the postal codes and display them in a web page by a count of 10 rows.

I have a problem in displaying the postal codes and its count in sets of 10. The data will be picked based on the count from Higher to Lower postalcodes based on its count.

The query I used is

select top 10 postalcode, count(parcels) as Cnt
from TableParcel
group by postalcode
order by Cnt desc

This code will be displaying the first 10 postalcodes and will be displayed in the web page.

But I want to find out the next top 10 postal codes that does not belong to the first top 10 postal codes.

How can I do this?

View 8 Replies View Related

How To Show Only Records That Belong To The Logged In User.

Sep 28, 2007

Hello.I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:http://forums.asp.net/p/1159666/1913519.aspx#1913519http://forums.asp.net/p/1161930/1924264.aspxhttp://forums.asp.net/p/1116601/1732359.aspx#1732359http://forums.asp.net/t/1104718.aspxhttp://forums.asp.net/p/1096290/1655706.aspx#1655706http://forums.asp.net/p/1110162/1707952.aspx#1707952 Basically, I need a DropDownList to display only projects for which the logged in user is assigned as leader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code?  * Here is the definition of the SqlDataSource: <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>" SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @Leader)" OnSelecting="SqlDataSource5_Selecting"> <SelectParameters> <asp:Parameter Name="Leader" Type="Object" /> </SelectParameters> </asp:SqlDataSource>  * Here is the definition of the SqlDataSource5_Selecting method:   protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters("@Leader").Value = loggedInUserId; } where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey; Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId; in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much. 

View 11 Replies View Related

Two Different Database Belong To Two Different Applications Stay In The Same Server

Aug 17, 2000

Hello....

Does anyone know if two different database belong to two different applications stay in the same physical SQL server 7.0 with one server license would affect their performance?

Would the performance be better off if each database stored in different physical SQL Server 7.0 with different server license?

Thank you in advance!

View 1 Replies View Related

Can You Backup And Restore Tables That Belong To A Specific Schema

Apr 30, 2008

Hi all,

does anyone know of a way in which you can backup and restore objects that belong to a specific schema?

So in this example, I€™d want to only back up and be able to restore the tables that belong to schema 1, leaving Schema2 stuff alone.

--DB
----Schema1.Table1
----Schema1.Table2
----Schema2.Table1
----Schema2.Table2

Thanks in advance,

Andy.

EDIT: I forgot to add that I want to backup the tables and the data contained within.

View 4 Replies View Related

How Can One User View Other Users That Belong To A Database Role?

Nov 15, 2006

After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.

In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.

In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.

Any advice on how to duplicate the behavior from SQL2000?

View 8 Replies View Related

Categories...

Feb 17, 2007

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.
 
Thanks
Fdo.

View 2 Replies View Related

How To Add Sub-categories???

Oct 27, 2004

Hi all,

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.

TIA

View 3 Replies View Related

Categories And Sub Categories

Apr 23, 2008

Hello,

I am working on a web site that makes documents available to students.
I am using tags to classify each document content.

So I have the following tables:
Documents, Tags, DocumentsTags

I also need to categorize the documents:

Subject
|---- Level
|----- Year

For example:

Document_1 - Math > University Level > Second Year

How can I create a table to create such classification?

Thank You,
Miguel

View 2 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

SQL Agent Job Categories

May 6, 2005

Hi,

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.

Do i just insert into sysCategories?

thanks

View 2 Replies View Related

Synchronize Categories

Sep 13, 2007

Hi,

I am trying to create a type of synchronization but I am having a lot of problems to make this work.

I have 3 tables:

CREATE TABLE Articles (ArticleId INT, ArticleText NVARCHAR(512))

CREATE TABLE Categories (CategoryId INT, CategoryText NVARCHAR(512))

CREATE TABLE CategoriesInCategories (ArticleId INT, CategoryId INT)

Consider the following tables content:

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


I need to create the following procedures:

CreateArticle

Create Procedure [CreateArticle]
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End

This procedure should the the following:

1. Create the article in table Articles

2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.

For each category check if there is already one with the same CategoryText in table Categories.

If there is just get the CategoryId and add a new record into CategoriesInArticles with ArticleId and found CategoryId.

If there isn't any then create the new Category into table Categories and then add the new record to CategoriesInArticles.


DeleteArticle

Create Procedure [DeleteArticle]
@ArticleId INT
As
Begin
...
End

This procedure should the the following:

1. Delete the article in tables Articles and CategoriesInArticles given its ArticleId

2 .Delete categories from table Categories that are no longer associated with any other article (in table CategoriesInArticles)


UpdateArticle
Create Procedure [UpdateArticle]

@ArticleId INT,
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End

This procedure should the the following:

1. Update the values into Articles Table

2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.

I think the easiest way is to delete all the categories from ArticleId as it is done in DeleteArticle.

Then It creates the new categories as it is done in CreateArticle.

Consider I create a new article:

EXEC CreateArticle @ArticleText = "Book launched at sports event" @ArticleCategories = "Entertainment, Business"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

3 Book launched at sports event

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

3 Business

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

3 2

3 3

NOTE: Because there is no category named Business in Categories this must be created.

Then I would updated it:

EXEC UpdateArticle @ArticleId = "3" @ArticleText = "Book launched at sports event" @ArticleCategories = "Sports, Tech"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

3 Book launched at sports event

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

4 Tech

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

3 1

3 4


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

Could someone, please, help me out with this?

Thank You Very Much,

Miguel

View 4 Replies View Related

Search Categories

Oct 24, 2007

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

But instead of two columns

Names Names
A B
A B
A B
A


Just the One
Names
A
A
A
A
B
B
B

View 2 Replies View Related

Multiple Categories In Same Row

Apr 20, 2007

Hello folks,

I've been pounding on this for a while, after countless searching I have to break down and ask for help.

I have a table set up like so:

+-----+------------+---------------+------------+
| id | title | path | category |
+-----+------------+---------------+------------+
| 1 | Firemen | firemen.pdf | CGL |
| 2 | Garages | garages.pdf | E&O |
| 3 | Auto | autos.pdf | EPLI |
| 4 | Garages | garages.pdf | CGL |
| 5 | Houses | houses.pdf | E&O |
+-----+------------+---------------+------------+



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!

Erik

View 2 Replies View Related

How To Get The Top 10 Categories In Chart

Sep 27, 2007



Hi,

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

Thank you

best regards,
Tanipar

View 6 Replies View Related

Database Design Of Categories

Nov 24, 2007

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. 

View 4 Replies View Related

Assigning To Multipule Categories

Aug 18, 2004

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.

Thanks in Advance,
Aaron Hawn (aaron@ionzion.net)

View 2 Replies View Related

Counting Items In Categories

Oct 28, 2007

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

View 2 Replies View Related

Design Issue (Sub Categories And Others)

Jun 15, 2008

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.

View 2 Replies View Related

Need To Select No Product Categories

Jan 19, 2014

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

View 3 Replies View Related

A Table/column To Table/column Data Check (was Help Please, SQL Something Simple)

Sep 15, 2006

Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

View 6 Replies View Related

How To Create Categories Tree With Subcategories ?

Dec 17, 2006

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 (??)

View 1 Replies View Related

How To Create Categories Tree With Subcategories ?

Dec 17, 2006

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 (??)

View 4 Replies View Related

Adding Record To Multiple Categories

Jun 16, 2006

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

@UNIT_IDint,
@F_TYPE varchar(20),
@Titlevarchar(100),
@Keywords ntext = NULL,
@Descriptionntext = NULL,
@FileNamevarchar(50)


AS
BEGIN

SET NOCOUNT ON
DECLARE @NewFOrmID INT
INSERT FORMS

(
UNIT_ID,
F_TYPE,
Title,
Keywords,
Description,
FileName,
RevDate
)

VALUES
(
@UNIT_ID,
@F_TYPE,
@Title,
@Keywords,
@Description,
@FileName, GetDate()
)

SELECT @newFormID = @@IDENTITY
SELECT newFormID = @newFormID
END


GO

View 1 Replies View Related

Finding Missing Items That Are Not In Categories

Aug 25, 2014

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.

View 11 Replies View Related

Application / User Defined Categories?

Mar 9, 2004

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?

Mike B

View 4 Replies View Related

Ascending Categories And Ascended Subcategories

Apr 18, 2014

I've been trying some examples on line and have not been able to get this to work.

Here is my table followed by what I am trying to output.

Id | ParentId | CategoryName

33 | 0 | Fruits
34 | 0 | Vegetables
35 | 0 | Meats
37 | 33 | Grapes
38 | 33 | Oranges

[Code] ....

I want to output this for a dropdownlist in C# with ascending categories and ascended subcategories

48 0 Electronics
33 0Fruits
3933 -Apples
4033 -Bananas
3733 -Grapes
3833 -Oranges

[Code] ...

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved