MS SQL Server :: Counting Only Countries That Can Sell A Product Category?

Product/Category/ProductCategory Scenerio

Mar 26, 2008

Here is my problem, I have three simple tables

Products
Categories
ProductCategories

Each product can be assigned to more than one category, hence the link table ProductCategories

Let's assume I have these categories in the category table
-Stationary
-Office
-Scool

I need an SQL query that enables me to find a list of products that has been assigned to the category "Stationary" AND "Office"

not either, but both.

How do I go about this? Thanks a lot in advance :(

View 3 Replies View Related

Counting Items In A Hierarchical Category And Subcategories

Mar 30, 2008

I have two tables, one is a list of categories, the other a list of items listed in the categories. The category table is self-referencing through a ParentID column. Top-level categories have ParentID = 0.
Categories========ID intParentID intCatName varchar(30)Items====IDCategoryIDItemID
There is a third table that links to items through ItemID, but this is not important for this problem!
What I want to do is create a stored SQL procedure. This procedure simply pulls rows from the category table and counts the number of items that are in it. This is straighforward enough using COUNT as a "virtual column", but the difficulty is counting the items in the category but also the items in any subcategories.
An end result with two top-level categories and three subcategories in each might look like:
- Category 1 (20)---- Subcategory 1.1 (10)---- Subcategory 1.2 (5)---- Subcategory 1.3 (5)- Category 2 (14)---- Subcategory 2.1 (3)---- Subcategory 2.2 (4)---- Subcategory 2.3 (7)Hence the difficult bit is getting the total number in brackets for each category with subcategories.
What I have at the moment is:
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE Items.CategoryID = Categories.ID) AS ItemTotalFROM Categories
What I would like is something along the lines of (pseudocode):
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE <Item is in category or subcategory>) AS ItemTotalFROM Categories
I don't particularly want to use temporary tables and I definitely do not want to do any of this retrieval in my application - it needs to come straight from the database.
Thanks!

View 2 Replies View Related

Category/Parent Category Design And Querying

Jun 29, 2005

Hi,

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.

View 5 Replies View Related

List Of Countries

Feb 5, 2008

Hello All,
I need to download the table containing the list of the database in sql server 2005, where can this be downloaded
thankx
Bibek

View 1 Replies View Related

Countries && Cities Tables

Jan 19, 2007

I need to have tables of world countries and cities to use in a user registration form, I searched the Internet for scripts,xml,excel files for countries & cities lists but I couldn't find. Can anybody tell me where to get them from?

View 6 Replies View Related

Dose MS Sell SQlSerever With Student Rate?

Apr 4, 2006

Dose MS sell SQlSerever with student rate? If so, which website do Ibuy from?

View 10 Replies View Related

Stores In Australia That Sell SQL Server 2005 Developer Edition

Oct 25, 2007

Does anyone know which stores in Australia sell SQL Server 2005 Developer Edition. I want to buy it from a store not online, thanks...

View 1 Replies View Related

Transact SQL :: Get All Children Of A Product With Product ID In Result Set

Aug 30, 2015

I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.

DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT)
INSERT INTO @Hierarchy VALUES (123, 234)
INSERT INTO @Hierarchy VALUES (234, 456)
INSERT INTO @Hierarchy VALUES (456, 678)
INSERT INTO @Hierarchy VALUES (678, 891)
INSERT INTO @Hierarchy VALUES (891, NULL)

[Code] .....

View 3 Replies View Related

Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category

Sep 21, 2006

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 Related

TOP N Value Per Category

Oct 26, 2006

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

Breakdown By Category

Dec 22, 2005

Let's say you a 1000 records in the Employees table, who are spread over 40 different cities.
How would you get a breakdown of how many employees in each city ?

Do I have to loop with a Count(*) for each CityID, or something ?

There must be a more straightforward method.

View 1 Replies View Related

Top 3 For Every Category With Group By

Mar 6, 2006

hi.

I have a table to store salesman's performance..

as you can see at structure.jpg, Tom sold total 18 Processors (5 different modals) and sold 11 mainboards (4 different modals)

I want to report that
what are the TOP 3 Processor and Mainboard modals that Tom sold with the same query

I tried different group by methods. also subqueries.
I could't do

you see the results I need at result.jpg

View 1 Replies View Related

HAVING (COUNT(category) &> 1) , Not Only 1 Row

Sep 25, 2006

i am using this code :

SELECT MAX(user) AS lastuser, category
FROM journal
GROUP BY category
HAVING (COUNT(category) > 1)

it works but returns 1 line by category >1

i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1

if 1 category has only 1 user i must not keep it

i am not shure to be clear :-)

thank you for helping

View 8 Replies View Related

Category Tables

Feb 25, 2004

What are the thoughts on using a categoy table? What I mean by this is to have a table used to hold simply a category identifier for other table(s).

Eg:

Hardware Table
Hadware (PK) | Category (FK) | Description | Finish | etc....
------------------------------------------------------------------------------
P1 | Plate | 4"x4"x1/4 plate | Painted |

HardwareCategories
Category (PK)
-----------------
Plate
Channel
Angle

Should this be done or simply provide a category field in the hardware table? I like the extra table because it kind of ensures that a user doens't add a plate with a category value "Plate", another user adds "plate", and another user adds "Plates", etc....

What are your thoughts if any?

Mike B

View 1 Replies View Related

Update Sys Category

Jan 27, 2008

I'm trying to remove a read only/stand by database that I believe was a messed-up attempt at log shipping. When I go through the interface it says 'cannot remove database because it is set up for replication'. If I try and 'directly update' the sys tables (yea I know you can't do that anymore) I get the error:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

So my dilema is I just want to delete a database that was attempting to do log shippinig and is now stuck in read only/standby. How can this be done in SQL 2005?

Thanks,
Phil

View 18 Replies View Related

Trying To Select Newest Row For Each Category...

Feb 6, 2004

Hi everyone -

I am building from the Time Tracker Start Kit, trying to get a better feel for how MS thinks we should do things.

In my editing, I have built a new Stored Procedure, trying to pull the newest entries for a specified person.

I have the following that will return all entries for a specific person, sorted by date, newest first:

SELECT
EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
ManagerUserID, TT_Categories.Abbreviation AS CatShortName
FROM
TT_EntryLog
INNER JOIN
TT_Categories
ON
TT_EntryLog.CategoryID = TT_Categories.CategoryID
INNER JOIN
TT_Projects
ON
TT_EntryLog.ProjectID = TT_Projects.ProjectID
WHERE
UserID = @UserID
ORDER BY
EntryDate Desc

This will return something like:

EntryLogId Description Duration EntryDate ProjectID CategoryID CategoryName ProjectName ManagerUserID CatShortName
14Can type date in date... .00 2004-02-04 10:28:00116Pros ITS Project Management App 3 Pros
12Changed "Entry Date"... .00 2004-02-03 13:28:00116Pros ITS Project Management App 3 Pros
13Added default button ... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
11Removed hours per p... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
6Isn't this cool .00 2004-02-02 00:00:00229Pros Knowledge Base 3 Pros
9Added week-by-week... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
10Fixed Update comma... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
1Built initial framewor... 6.00 2004-01-30 00:00:00116Pros ITS Project Management App 3 Pros
5Adding up to 8 hours... 2.00 2004-01-30 00:00:00229Pros Knowledge Base 3 Pros
3Debugged - fixed a f... 1.00 2004-01-23 00:00:00229Pros Knowledge Base 3 Pros

What I would like to accomplish is to return only the newest entry for each ProjectID (so in the above example, there would only be 2 entries, EntryID 14 and 6)

Any ideas?

Thanks in advance-

View 4 Replies View Related

Select Last Entry Per Category?

Nov 8, 2005

Hello,

I am trying to select the last entry (by date) for each category in a table.

For example, if my table had the following fields;

id, category, product, datePosted,....

...how would I select the last product for each category posted by date?

Any guidance is appreciated.

Thanks,
AC

Probably a simple solution, but can't find it my brain right now!

View 7 Replies View Related

Running Sum - How To Get It To Restart For Each Category

Jun 6, 2012

I was able to get a running some to work, but i cant seem to get it to restart for each category... for example this is what I have...

ID Name Return Run. Sum
1 aaa 0.1 0.1
2 aaa 0.2 0.3
3 aaa 0.3 0.6
4 aaa 0.1 0.7
5 bbb 0.5 1.2
6 bbb 0.4 1.6
7 bbb 0.1 1.7
8 bbb 0.3 2
9 bbb 0.2 2.2

How do i get it to start over at bbb??? Here is the code in access 2010:

Format(1000+(DSum("Return","Monthly Performance Dates","[ID]<=" & [ID] & "")*1000),"00.00")

View 1 Replies View Related

Query To Count AGE By Category

Sep 17, 2013

How to write a query to count AGE by category (Exec or non Exec)

sample are as follow:

----Age Group---------
Category: Age <25 age 25-35 age 45-50 Total
Exec 2 1 3
Non Exec 10 5 5 20

Grand total: 23

View 2 Replies View Related

10 Random Records, 1 From Each Category

Dec 1, 2006

I'm not sure if this is a completely dumb question, but please humor me:) I have a table of records, called Records, each of which has aCategory_ID that places it in a specific category; the details of thecategories are stored in another table called Category. What I need todo is retrieve a recordset that contains one record from each category,but where the records that are retrieved are random. I know how toretrieve one or more random records using "order by NewID()", but havenot been able to work out how to get one random record from eachcategory.Any assistance in this puzzler will be HUGELY appreciated!ThanksFEB

View 7 Replies View Related

Select Records Under Sub Category

Aug 30, 2007

I got 3 table below

Region
---------------
RegionID
ParentID
RegionName

Cuisine
--------
CuisineID
CuisineName

Restaurant
-----------
RetaurantID
CuisineID
RegionID

Data

RegionID RegionName ParentID
1 RN_1 Null
2 RN_2 Null
3 RN_1_1 1
4 RN_1_2 1
5 RN_2_1 2

CuisineID CuisineName
1 CU1
2 CU2

RestaurantID CuisineID RegionID
1 1 4
2 1 2
3 2 5
4 1 3

What I would like is to write a stored procedure to browse the restaurant by either regionname or cuisinename.
I tried to create the view from those three table and create the stored procedure to search from the view based on criterias
I tried to search for regionName = RN_1 & CuisineName = 2 the result is empty. It is true because there is no restaurant under that region however what I want to have is list all restaurants under that RegionName children e.g
RN_1 has RN_1_1 & RN_1_2 & RN_1_3 so the result should be displayed as

RestaurantID CuisineID RegionName ParentID
1 1 RN_1_2 1
4 2 RN_1_1 1

Could anyone help me to do so. Thank you

View 2 Replies View Related

Category Column In Sysobjects

Jul 25, 2007

Everyone,

Is there a reference to describe the relevance of the Category column in the sysobjects table? I'm trying to track down stored procedures that are set to automatically run at startup and the only thing I can find that is consistent about those that are set to autorun is that they have a value of 16 in the Category column. Is this consistent? What else may I glean from this column?

Thanks

Tim

View 3 Replies View Related

Need Category Table Implementation Help

Apr 20, 2008

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

View 5 Replies View Related

Transact SQL :: How To Get Top 5 Based On A Category

Jun 4, 2015

I have this following query,

select [Parent Name],[ID],[Year],[Sales Name],
sum([Total VtM]) as 'Total Sales'
from RegData
group by [Parent Name],[ID],[Year],[Sales Name]
order by [Total Sales] desc

I need to modify this query to get the top 5 of each category based on Total Sales amount.

View 13 Replies View Related

SELECT CategoryID FROM Category WHERE Name Like %’@Name’%

Mar 16, 2007

Note:Codes
with in [] are optional u need not to read

I
am getting Incorrect syntax near '@Name'.

[

Dim strSQL As String

        Dim
param As New
SqlParameter

        Dim
AdaCategory As New
SqlDataAdapter

        Dim cmd as New SqlCommand()

]

strSQL = " SELECT CategoryID FROM Category   WHERE Name
like %@Name%"

I
am getting Incorrect syntax near '@Name'.

I
also tried

strSQL = " SELECT CategoryID FROM Category   WHERE Name
like %’@Name’%"

still Error :(

[ cmd.CommandText = strSQL

param =
cmd.Parameters.Add("@Name",
SqlDbType.NVarChar)

            param.Value = catId

            cmd.CommandText = strSQL


AdaCategory.SelectCommand = cmd

AdaCategory.Fill(DTable)

]

Help me please……….

View 2 Replies View Related

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?
Thanks
David

View 3 Replies View Related

Deleting Entries Under A Category That Is Being Deleted???

Sep 11, 2007

Hello. I have a simple project using 3 tables: Categories, Subcategories, and Items. I have just gotten my insert, edit, and delete functions to work, but I've noticed a problem I hope someone can help with:
When I delete a certain category (lets say "Restaurants"), the subcategories and items that were in that category still remain in the database/table. (If I delete "Restaurants", the subcategories "Italian", "Seafood", etc - as well as any items in those subcategories - are not deleted).
So what would I need to do to delete any Subcategory that is in the deleted Category (shares a CategoryID) and then delete all Items in those Subcategories?
For reference, all Subcategories in a Category have reference to that CategoryID, and all Items in a Subcategory have a "SubcategoryID" field. I understand that I need to traverse the tables and remove all Subcategories with the CategoryID being deleted, but since the Items do not have a reference to the CategoryID, how would I delete those as well?

Thanks to whoever can help me out here!

View 4 Replies View Related

Display Of Data Category Wise

Dec 12, 2007

Hi,
I have a requirement where I need to display the items category wise
I have 3 tables,
one stores category_id, parent_id and category_name
second table stored item_id,item_name and proce
3rd table stores category_id and item_id
I need to display the values like this
Category name, Item name
with first parent category ans ites items should be displayed and next sub category and its item
By category table with contain 2 levels of sub category
i.e Category1
       Category11
        Category111
any help with the query would be much appreciated, I wrote the query something like this....
select c.category_name,parent_id,item_name from category c,items i,Category_item ci where
ci.item_id=i.item_id and c.category_id=ci.category_id order by parent_id, category_nameBut it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2
Thanks

View 2 Replies View Related

Articles In More Than One Category - How To Organize Tables?

Jun 10, 2005

Greetings,I have one table, named Article, and one table name Category.The problem is, one Article could be in just one or in several categories.What is the best way to connect data between Article and Category according to fast search performance?I have several ideas:1. To have third cross table Article_Category with fields Article_ID and Category_ID, and search Article_Category table 2. To have several INTEGER columns in Article table (like Category_ID1, Category_ID2,..) and search those columns3. Add one VARCHAR field in Article table where I could write Category ID's delimited by some character (e.g. by comma), and do text search in only that column.What is recommended for solving problems like this?

View 7 Replies View Related

Sum Hours By Category And Then Group By Week Of

Jul 25, 2013

I need to sum the attendance hours by category and then group by 'Week of'. The 'Week of' start date is defined by the Monday in that week but Sunday is works too. If the Category Values are in ’Art’ or ‘PE’, they need to be combined into Non Educational. I also need to be able to flag the day(s) a student reaches 120 hours.

My table which is structured like this:

CREATE TABLE Attendance (
DOP_ID int,
Category varchar(20),
Title varchar(20),
Date datetime,
Hours int,
)

[Code] ....

I need an end result which looks like this:

ID Category Week of Total Hours
4504498 GED Program 7/1/2012 26
4504498 GED Program 7/8/2012 23
4504498 High School 7/1/2012 19
4504498 High School 7/8/2012 28
9201052 Non Educational 7/15/2012 30

ID Day_120_Hours_Reached
356485 6/30/2012
356485 11/15/2012
555666 10/12/2012
555666 2/25/2013

I have been looking for examples of a Week function that will pull out the 'week of' from a date using MS Sql Server and I can't find much info.

View 6 Replies View Related

How To Group/list Top 3 Of Each Category W/o Using Union?

Jul 20, 2005

Hello,So my table contains say 100,000 records, and I need to group thecategories in fld1 by the highest count of subcategories. Say fld1contains categories A, B, C, D, E.All of these categories contain subcategories AA, AB, AC, AD,...AJ, BA,BB...BJ, CA, CB, CC...CJ, etc in fld2.I am counting how many subcategories are listed for each category. LikeA may contain 5 of AA, 7 of AB, 3 of AC, 11 of AD...1 for the rest and20 of AJ. B may contain 2 of BA, 11 of BB, 7 of BC, and 1 for the rest.I want to pick up the top 3 subcategory counts for each category. Wouldlook like this:Cat SubCat CountA AJ 20A AD 11A AB 7B BB 11B BC 7B BA 2So event though each category contains 10 subcategories, I only want tolist the top 3 categories with the highest counts as above. If I justdo a group by and sort I can get this:Cat SubCat CountA ... ...AAAAAA...B ... ...BBBBB...But I just want the top 3 of each category. The only way I can think ofto do this is to query each category individually and Select Top 3, andthen Union these guys into one query. The problem is that I have tohardcode each category in the Union query. There may be new categoristhat I miss. Is there a way to achieve what I want without using Union?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Problem With Category Grouping In SSRS

Apr 3, 2008

I have an SSRS chart which has 4 to 5 levels of categories (for eg- Country,State,City,Street) levels , Now when i select the street it shows groups starting from Country then State, City, and then street . Because of these grouping sometimes the label is not appearing properly its gets truncated between lines , Is there any way to avoid showing the groups only show the last group for example as per the above example (Instead of showing State, City, and then street ) just show the Street itself so that all grouping lines get avoided and chart looks better without any label truncation

View 8 Replies View Related