Sql-server – Difficult Query: Can Only Modify The WHERE Clause

sql server

Table tblDistResell (the first row is the column headers):

DISTRIBUTOR RESELLER    STATE
ABC         BestBuy     CA
ABC         RadioShack  NJ
XYZ         BestBuy     CA
XYZ         RadioShack  MO

Query:

SELECT Distributor
WHERE
(
(Reseller = 'BestBuy' AND [State]='CA') OR (Reseller = 'RadioShack' AND [State]='NJ')
);

Resultset:

DISTRIBUTOR  RESELLER    STATE
ABC          BestBuy     CA
ABC          RadioShack  NJ
XYZ          BestBuy     CA

When EVERY row for a distributor is returned, we want the count (or the resultset; whatever's easier).

But if only ONE (or more) distributor record(s) aren't returned, we want to ignore all of that distributor's results.

In other words, given the example above, we want to see either,

DISTRIBUTOR  RESELLER    STATE
ABC          BestBuy     CA
ABC          RadioShack  NJ

or

count == 2

(No XYZ records, since the RadioShack, MO record is missing from the resultset.)

Here's one more example for clarity…

Let's use the same table, tbltblDistResell, but a modified query:

SELECT Distributor                                              
WHERE                                               
(                                               
(Reseller = 'BestBuy' AND [State]='MO') OR (Reseller = 'RadioShack' AND [State]='NJ')
);

In this case, the following is returned:

DISTRIBUTOR  RESELLER    STATE
ABC          RadioShack  NJ

But since there is some ABC data that wasn't returned, we want to see either:

(0 row(s) affected)

or

count == 0

The challenge is that the tool we're using only allows us to modify the WHERE clause!

Any help would be greatly appreciated.

Greg

Best Answer

Count the number of rows for every distribution and compare that to the number of rows returned for a distribution where you apply your filter.

declare @T table
(
  Distributor char(3),
  Reseller varchar(10),
  State char(2)
);

insert into @T values
('ABC', 'BestBuy',    'CA'),
('ABC', 'RadioShack', 'NJ'),
('XYZ', 'BestBuy',    'CA'),
('XYZ', 'RadioShack', 'MO');

select T1.Distributor
from @T as T1
where (
      select count(*)
      from @T as T2
      where T1.Distributor =T2.Distributor
      ) = 
      (
      select count(*)
      from @T as T3
      where T1.Distributor =T3.Distributor and  
            (
            T3.Reseller = 'BestBuy' and T3.State='CA' or 
            T3.Reseller = 'RadioShack' and T3.State='NJ'
            )
      );

select T1.Distributor
from @T as T1
where (
      select count(*)
      from @T as T2
      where T1.Distributor =T2.Distributor
      ) = 
      (
      select count(*)
      from @T as T3
      where T1.Distributor =T3.Distributor and  
            (
            T3.Reseller = 'BestBuy' and T3.State='MO' or 
            T3.Reseller = 'RadioShack' and T3.State='NJ'
            )
      );
Related Question