Special select criteria

[pleashelp]

I saw a thread last week that was similar to this, but didin't really answer my question. So, I'll try to explain my particular problem and see if anyone can help me.

I am using SQL 2000 and this is my problem. Suppose I have these 5 customers and their orders. They can order many different bats, many different resins and many different gloves. These are their orders:

Customer1
Bat_1
Bat_2
Resin_1
Resin_2
Glove_1
Glove_2
Hat_1
Hat_2

Customer2
Bat_1
Resin_1
Resin_2

Customer3
Resin_1

Customer4
Resin_1
Resin_2
Glove_1
Glove_2
Hat_1
Hat_2

Customer5
Bat_2

This is what I want to do:
Select * customers where
They bought any bat ONLY but no other items OR
they bought any bat AND any resin ONLY with no other items

Do not select customers where
They did not buy any bat
They bought resin_1 or resin_2 but not any bat
They bought any bat as well as other items

So I want to return Customer2 and
Customer5
But do not return Customer1,
Customer3, or
Customer4
My Query is like this:

Select customer.CustName,
Equipment.BaseballEquip
From Customer
Inner join Equipment on
Equipment.Cust_ID= Customer.Cust_ID
Where
BaseballEquip Like ‘Bat%’ or BaseballEquip like ‘Resin%’

But this gives me customers even if they bought something in addition to any bat or if they bought something in addition to any bat and any resin. It doesn't limit my results to my particular criteria. So I am really stumped. Can anyone out there help me?

 

[r937]

try this --

SELECT customer.CustName
     , Equipment.BaseballEquip     
  FROM Customer            
INNER 
  JOIN Equipment 
    ON Equipment.Cust_ID = Customer.Cust_ID   
 WHERE Customer.Cust_ID IN
       ( SELECT Cust_ID
           FROM Equipment
         GROUP
             BY Cust_ID
         HAVING COUNT(CASE WHEN BaseballEquip Like 'Bat%' 
                             OR BaseballEquip Like 'Resin%' 
                           THEN 'ok' ELSE NULL END) > 0
            AND COUNT(CASE WHEN NOT
                              ( BaseballEquip Like 'Bat%' 
                             OR BaseballEquip Like 'Resin%' )
                           THEN 'no' ELSE NULL END) = 0 )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

Phooey. I just spent the last 20 minutes writing an explanation for a query that is VERY similar to Rudy's query. Raise your hand if you want to see the explanation.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

I would George, your explanations are always very good.

Simi

 

[gmmastros]

There are likely to be several different ways to write this query. I will try the best I can to describe how I would approach this.

COUNTS

I would try to carefully use counts to determine if a customer should be returned by the query. Counts of interest are:

1. How many bats were sold.
2. How many resins were sold
3. How many "other" things were sold.

From the sounds of it, we want to return customers with BatCount > 0 and "other" count = 0. As long as the other count does not include resin, we should be ok. This assumes that I understand the logic, which may or may npot be true.

So now the question becomes, how do we determine these counts for each customer?

The equipment table has everything we need to do this. I started with a simple COUNT query like this.

[tt][blue]
Select Cust_ID,
Count(*) As TotalCount
From Equipment
Group By Cust_ID
[/blue][/tt]

That query should return a row for each customer and a count of the total number of items sold. Now, I want to know how many bats were sold, so I added this:

[tt][blue]
Select Cust_ID,
[blue]Count(Case When BaseballEquip Like 'bat%' Then 1 Else NULL End) As BatCount,[/blue]
Count(*) As TotalCount
From Equipment
Group By Cust_ID
[/blue][/tt]

Count(*) simply returns a total count. When you do not use * in the parenthesis to the Count function, the count is only incremented for non-null values. In this case, if BaseballEquip is not like 'bat%', I return null. At this point, we should have a count of the number of bats that were sold to each customer.

Now let's add a couple more columns for resin and other.


[tt][blue]
Select Cust_ID,
Count(Case When BaseballEquip Like 'bat%' Then 1 Else NULL End) As BatCount,
Count(Case When BaseballEquip Like 'resin%' Then 1 Else NULL End) As ResinCount,
Count(Case When BaseballEquip Like 'bat%' Then Null
When BaseballEquip Like 'resin%' Then Null
Else 1 End) As OtherCount,
Count(*) As TotalCount
From Equipment
Group By Cust_ID
[/blue][/tt]

When you run the above query, you should see that the BatCount + ResinCount + OtherCount will add up to the total count. Having the counts is a great step towards solving this problem.

Now all we need to do is to filter out the rows where BatCount > 0 and OtherCount = 0. To do this, we can use the Having clause, like this:

[tt][blue]
Select Cust_ID,
Count(Case When BaseballEquip Like 'bat%' Then 1 Else NULL End) As BatCount,
Count(Case When BaseballEquip Like 'resin%' Then 1 Else NULL End) As ResinCount,
Count(Case When BaseballEquip Like 'bat%' Then Null
When BaseballEquip Like 'resin%' Then Null
Else 1 End) As OtherCount,
Count(*) As TotalCount
From Equipment
Group By Cust_ID
Having Count(Case When BaseballEquip Like 'bat%' Then 1 Else NULL End) > 0
And Count(Case When BaseballEquip Like 'bat%' Then Null
When BaseballEquip Like 'resin%' Then Null
Else 1 End) = 0
[/blue][/tt]

When you run this query, you should see a list of customer id's and their corresponding counts. Your original query showed customer name and equipment, so there is still more work to do. At this point, all we have done is filtered out the customers based on their purchases. Now it's time to return the rest of the data. To do this, I would recommend a derived table approach (especially since you are using SQL2000).

Something like this:

Select customer.CustName,
       Equipment.BaseballEquip
From   Customer
       Inner join Equipment on
         Equipment.Cust_ID= Customer.Cust_ID
       Inner Join (
         Select Cust_Id
         From   Equipment 
         Group BY Cust_ID
         Having  Count(Case When BaseballEquip Like 'Bat%' Then 1 Else NULL End) > 0
                 And Count(Case When BaseballEquip Like 'Resin%' Then NULL 
                                When BaseballEquip Like 'Bat%' Then Null 
                                Else 1 End) = 0
         ) As CustomerFilter
         On Customer.Cust_ID = CustomerFilter.Cust_ID

If any part of this does not make sense, please let me know and I will explain it to you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

[raises hand]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[pleashelp]

Thank you sooo much..I am going to work my way through this to be sure that I understand it, and if not, I'll ask additional questions.

 

[pleashelp]

OK..I am returning the correct data to this point using gmmastros query. That's great! Right up to the last part where I need to add in the other fields that I want to return. First of all, I apologize..I am using SQL 2005 not 2000. So..I don't quite understand that last section you gave. In addition to the Cust_ID, CustName, and BaseballEquip, I also want to return the Location and a purchase date within the last 6 months. Do I enclose the whole Count section inside of the select statement for the rest of the fields. I guess I am a little cloudy on just how to write this last part. I really appreciate all of the help!!!

 

[gmmastros]

What table are those columns (Location & PurchaseDate) in? Also, do you want additional filtering to occur based on these columns?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If you're using SQL Server 2005, then add extra fields to your query and remove group by. Convert your counts to count .. over (partition by Cust_ID)

-------------------
Also, I sent a solution similar to George's few days ago on a similar problem. But since then I finally re-discovered how these types of problems are called. The problems like this are called "relational division".

PluralSight Learning Library

 

[markros]

I also found the thread - it's thread183-1602193

PluralSight Learning Library