Help With Nested Query

I am having trouble with the following query.

Important Tables:
Product (table of products)
--ProductID
--ProductName

ProductCategories (Associates a Product with one or more categories)
--ProductID
--CategoryID

Category (table of categories that a product may fall under)
--CategoryID
--CategoryName

Information:

Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.

Attempted Solution:
SELECT * FROM Product
WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1))
AND
(ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))

This returned zero records though it should have returned the product that is in categories 1&2.

I would appreciate any help available.

Thank you,
-PatrickI am trying to create a query that will find all products that are in categories 1 & 2.do a regular many-to-many join, but use GROUP BY on the product, and HAVING to retain only those products which were in more than one category
select ProductName
from Category C
inner
join ProductCategories PC
on C.CategoryID = PC.CategoryID
inner
join Product P
on PC.ProductID = P.ProductID
where C.CategoryID in (1,2)
group
by ProductName
having count(*) > 1|||You are going to kick yourself, but the reason your query failed to return records is because you were trying to compare outer "ProductID"s to inner "CategoryID"s.

...WHERE (ProductID IN (SELECT CategoryID...???

You can rewrite your query more simply like this:

select Product.*
from Product
inner join ProductCategories Cat1 on Product.ProductID = Cat1.ProductID
inner join ProductCategories Cat2 on Product.ProductID = Cat2.ProductID
where Cat1.CategoryID = 1 and Cat2.CategoryID = 2

Use the DISTINCT keywork if the query returns multiple records.

Help with Joins

Hi All,
I'm trying to write a query that will return the number of products sold,
and the total value of each products sales.
Heres what I have so far:
SELECT COUNT(cartrows.idProduct) AS QtySold, cartrows.idProduct AS
idProduct
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct
ORDER BY cartrows.idProduct
This works Ok, but I only want to return orders where the status is either
1, 2 or 7. What I came up with is below, which returns one line per
idProduct, per OrderStatus:
SELECT TOP 100 PERCENT COUNT(cartrows.idProduct) AS QtySold,
cartrows.idProduct AS idProduct,
carthead.orderStatus
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct, carthead.orderStatus
HAVING (carthead.orderStatus IN ('1', '2', '7'))
ORDER BY cartrows.idProduct
I guess this is the result I expect from this, I'm just not sure what I need
to do so I only get one line per idProduct, with the qty sold, only from
orders with an orderStatus of 1, 2 or 7.
Any help will be much appreciated. I've include table design statements
below.
Thanks!
Simon.
CREATE TABLE [carthead] (
[idOrder] [int] IDENTITY (1, 1) NOT NULL ,
[idCust] [int] NULL ,
[orderDate] [datetime] NULL ,
[orderDateInt] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[randomKey] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[subTotal] [float] NULL ,
[taxTotal] [float] NULL ,
[shipmentTotal] [float] NULL ,
[Total] [float] NULL ,
[shipmentMethod] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[lastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[customerCompany] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[phone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[address] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[city] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[zip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[shippingName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingAddress] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingZip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[paymentType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardNumber] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardExpMonth] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[cardExpYear] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardVerify] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[generalComments] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[orderStatus] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[auditInfo] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[storeComments] [text] COLLATE Latin1_General_CI_AS NULL ,
[storeCommentsPriv] [text] COLLATE Latin1_General_CI_AS NULL ,
[adjustAmount] [float] NULL ,
[adjustReason] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[discCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[discPerc] [float] NULL ,
[discTotal] [float] NULL ,
[shippingPhone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[handlingFeeTotal] [float] NULL ,
[idAffiliate] [int] NULL ,
[commPerc] [float] NULL ,
[otherFeeTotal] [float] NULL ,
[backOrder] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[idOrder]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [cartrows] (
[idCartRow] [int] IDENTITY (1, 1) NOT NULL ,
[idOrder] [int] NULL ,
[idProduct] [int] NULL ,
[sku] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[quantity] [int] NULL ,
[unitPrice] [float] NULL ,
[unitWeight] [float] NULL ,
[description] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[downloadCount] [int] NULL ,
[downloadDate] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[idDiscProd] [int] NULL ,
[discAmt] [float] NULL ,
PRIMARY KEY CLUSTERED
(
[idCartRow]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GODDL, great! Thanks!
Move the condition to the WHERE clause (untested, since you haven't provided
any sample data):
select count(cartrows.idProduct) as QtySold
,cartrows.idProduct as idProduct
from cartrows
inner join carthead
on cartrows.idOrder = carthead.idOrder
where (carthead.orderStatus in ('1', '2', '7'))
group by cartrows.idProduct
order by cartrows.idProduct
As I see it you want to restrict the result before the rows are grouped -
this is what WHERE does. The HAVING clause restricts results *after* the row
s
have been grouped.
ML
http://milambda.blogspot.com/|||Thank you for your reply - Especially the explanation of WHERE Vs HAVING.
Simon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0C331587-2374-4C79-A6BD-12F590E0FBC4@.microsoft.com...
> DDL, great! Thanks!
> Move the condition to the WHERE clause (untested, since you haven't
> provided
> any sample data):
> select count(cartrows.idProduct) as QtySold
> ,cartrows.idProduct as idProduct
> from cartrows
> inner join carthead
> on cartrows.idOrder = carthead.idOrder
> where (carthead.orderStatus in ('1', '2', '7'))
> group by cartrows.idProduct
> order by cartrows.idProduct
> As I see it you want to restrict the result before the rows are grouped -
> this is what WHERE does. The HAVING clause restricts results *after* the
> rows
> have been grouped.
>
> ML
> --
> http://milambda.blogspot.com/|||NP. Just remember which NG works for you. ;)
ML
http://milambda.blogspot.com/