JOIN Confusion

31 Aug 2006 10:27 PM

Willie Bodger

So, I have a query like this:

SELECT DISTINCT PCM.iOwnerID, PCM.chProductNumber, count(PCM.iHierarchyID)
AS Num
FROM (SELECT *
      FROM dbo.vOnyx_Products
      WHERE (iHierarchyID IN (199))) PCM LEFT OUTER JOIN
              (SELECT *
               FROM dbo.vOnyx_Products
               WHERE (iHierarchyID NOT IN (199))) NOTPCM ON PCM.iOwnerID =
NOTPCM.iOwnerID
WHERE (NOTPCM.iOwnerID IS NULL)
GROUP BY PCM.iOwnerID, PCM.chProductNumber
HAVING COUNT(PCM.iHierarchyID)>=10

Which will give me everybody with a specific category and only that category
(iHierarchyID=199) 10 or more times in their account. Now, I would like to
modify this so that it gives me everybody that has this category 10 times
AND has at least one of another specific category (iHierarchyID=195) in
their account. I have tried a number of options and none are quite right, so
if anybody out there has any suggestions for me...

Willie

31 Aug 2006 10:43 PM

Tom Cooper

I'm not sure why you were grouping by both OwnerID and ProductNumber in your
first query, but I did the same with mine. Also, you don't need a DISTINCT
when you have a GROUP BY since you can't possibly get duplicate rows. The
following query is not tested.

SELECT P.iOwnerID, P.chProductNumber, Count(P.iHierarchyID)
FROM dbo.vOnyx_Products P
WHERE P.iHierarchyID = 199 AND
EXISTS (SELECT * FROM dbo.vOnyx_Products OP
WHERE P.iOwnerID = OP.iOwnerID
AND OP.iHierarchyID = 195)
GROUP BY P.iOwnerID, p.chProductNumber
HAVING Count(P.iHierarchyID) > 10

Tom

Show quote

"Willie Bodger" <williebnospam@lap_ink.c_m> wrote in message
news:%23C4L5uUzGHA.4176@TK2MSFTNGP06.phx.gbl...
> So, I have a query like this:
>
> SELECT DISTINCT PCM.iOwnerID, PCM.chProductNumber, count(PCM.iHierarchyID)
> AS Num
> FROM (SELECT *
>      FROM dbo.vOnyx_Products
>      WHERE (iHierarchyID IN (199))) PCM LEFT OUTER JOIN
>              (SELECT *
>               FROM dbo.vOnyx_Products
>               WHERE (iHierarchyID NOT IN (199))) NOTPCM ON PCM.iOwnerID =
> NOTPCM.iOwnerID
> WHERE (NOTPCM.iOwnerID IS NULL)
> GROUP BY PCM.iOwnerID, PCM.chProductNumber
> HAVING COUNT(PCM.iHierarchyID)>=10
>
> Which will give me everybody with a specific category and only that
> category (iHierarchyID=199) 10 or more times in their account. Now, I
> would like to modify this so that it gives me everybody that has this
> category 10 times AND has at least one of another specific category
> (iHierarchyID=195) in their account. I have tried a number of options and
> none are quite right, so if anybody out there has any suggestions for
> me...
>
> Willie
>

31 Aug 2006 11:02 PM

Willie Bodger

Yeah, I sometimes add more than needed, generally end up removing it as I
refine things. That looks like it will work. I knew that it wasn't really a
difficult query, thanks! Oh, and you have to group by everything that is not
part of the having clause. At least that has been my experience due to the
type of function that it is.

Willie

Show quote

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:RsudncvNj-9m-GrZnZ2dnUVZ_sSdnZ2d@comcast.com...
> I'm not sure why you were grouping by both OwnerID and ProductNumber in
> your first query, but I did the same with mine. Also, you don't need a
> DISTINCT when you have a GROUP BY since you can't possibly get duplicate
> rows. The following query is not tested.
>
> SELECT P.iOwnerID, P.chProductNumber, Count(P.iHierarchyID)
> FROM dbo.vOnyx_Products P
> WHERE P.iHierarchyID = 199 AND
> EXISTS (SELECT * FROM dbo.vOnyx_Products OP
>     WHERE P.iOwnerID = OP.iOwnerID
>      AND OP.iHierarchyID = 195)
> GROUP BY P.iOwnerID, p.chProductNumber
> HAVING Count(P.iHierarchyID) > 10
>
> Tom
>
>
> "Willie Bodger" <williebnospam@lap_ink.c_m> wrote in message
> news:%23C4L5uUzGHA.4176@TK2MSFTNGP06.phx.gbl...
>> So, I have a query like this:
>>
>> SELECT DISTINCT PCM.iOwnerID, PCM.chProductNumber,
>> count(PCM.iHierarchyID) AS Num
>> FROM (SELECT *
>>      FROM dbo.vOnyx_Products
>>      WHERE (iHierarchyID IN (199))) PCM LEFT OUTER JOIN
>>              (SELECT *
>>               FROM dbo.vOnyx_Products
>>               WHERE (iHierarchyID NOT IN (199))) NOTPCM ON PCM.iOwnerID =
>> NOTPCM.iOwnerID
>> WHERE (NOTPCM.iOwnerID IS NULL)
>> GROUP BY PCM.iOwnerID, PCM.chProductNumber
>> HAVING COUNT(PCM.iHierarchyID)>=10
>>
>> Which will give me everybody with a specific category and only that
>> category (iHierarchyID=199) 10 or more times in their account. Now, I
>> would like to modify this so that it gives me everybody that has this
>> category 10 times AND has at least one of another specific category
>> (iHierarchyID=195) in their account. I have tried a number of options and
>> none are quite right, so if anybody out there has any suggestions for
>> me...
>>
>> Willie
>>
>
>

31 Aug 2006 11:45 PM

Tom Cooper

It is true that you must group by everything in the SELECT list that is not
in some aggregate function like Count(), Max(), Sum(), etc. It just seemed
unusual to me that you were grouping by ProductNumber, but not including
ProductNumber in your joins (Note that I said unusual, not necessarily
incorrect). For example, if OwnerID 1 has three products "A", "B", and "C"
and "A" has 8 rows all with HierarchyID = 199, "B" has 2 rows both with
HierarchyID = 199 and "C" only has 1 row which has HierarchyID = 195, all
three of those rows will be included in the result. If that is your
intended result, great, I was just mildly worried that you weren't getting
what you expected.

Tom

Show quote

"Willie Bodger" <williebnospam@lap_ink.c_m> wrote in message
news:eMYhhCVzGHA.1536@TK2MSFTNGP02.phx.gbl...
> Yeah, I sometimes add more than needed, generally end up removing it as I
> refine things. That looks like it will work. I knew that it wasn't really
> a difficult query, thanks! Oh, and you have to group by everything that is
> not part of the having clause. At least that has been my experience due to
> the type of function that it is.
>
> Willie
>
> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
> news:RsudncvNj-9m-GrZnZ2dnUVZ_sSdnZ2d@comcast.com...
>> I'm not sure why you were grouping by both OwnerID and ProductNumber in
>> your first query, but I did the same with mine. Also, you don't need a
>> DISTINCT when you have a GROUP BY since you can't possibly get duplicate
>> rows. The following query is not tested.
>>
>> SELECT P.iOwnerID, P.chProductNumber, Count(P.iHierarchyID)
>> FROM dbo.vOnyx_Products P
>> WHERE P.iHierarchyID = 199 AND
>> EXISTS (SELECT * FROM dbo.vOnyx_Products OP
>>     WHERE P.iOwnerID = OP.iOwnerID
>>      AND OP.iHierarchyID = 195)
>> GROUP BY P.iOwnerID, p.chProductNumber
>> HAVING Count(P.iHierarchyID) > 10
>>
>> Tom
>>
>>
>> "Willie Bodger" <williebnospam@lap_ink.c_m> wrote in message
>> news:%23C4L5uUzGHA.4176@TK2MSFTNGP06.phx.gbl...
>>> So, I have a query like this:
>>>
>>> SELECT DISTINCT PCM.iOwnerID, PCM.chProductNumber,
>>> count(PCM.iHierarchyID) AS Num
>>> FROM (SELECT *
>>>      FROM dbo.vOnyx_Products
>>>      WHERE (iHierarchyID IN (199))) PCM LEFT OUTER JOIN
>>>              (SELECT *
>>>               FROM dbo.vOnyx_Products
>>>               WHERE (iHierarchyID NOT IN (199))) NOTPCM ON PCM.iOwnerID
>>> = NOTPCM.iOwnerID
>>> WHERE (NOTPCM.iOwnerID IS NULL)
>>> GROUP BY PCM.iOwnerID, PCM.chProductNumber
>>> HAVING COUNT(PCM.iHierarchyID)>=10
>>>
>>> Which will give me everybody with a specific category and only that
>>> category (iHierarchyID=199) 10 or more times in their account. Now, I
>>> would like to modify this so that it gives me everybody that has this
>>> category 10 times AND has at least one of another specific category
>>> (iHierarchyID=195) in their account. I have tried a number of options
>>> and none are quite right, so if anybody out there has any suggestions
>>> for me...
>>>
>>> Willie
>>>
>>
>>
>
>