• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Can this be done in SQL (1 SQL statement)??

I have a table in Access called wholesalers_and_retailers, containing wholesaler_id & retailer_id.

Most wholesalers service only 1 retailer.  However, there are some exceptions.

I'm trying to identify wholesalers which service more than one retailer, but only if one of the retailers has a retailer_id = 337.

I can't figure out where to start.  

Should I first query all records which have a retailer_id= 337, and send the results to a temp table?  Then somehow join that list of wholesalers to the main table, but only if the retailer_id is NOT 337?

I'm at a a loss; any suggestions would be very much appreciated !!!

Thanks,
Mike
0
mike2401
Asked:
mike2401
  • 6
  • 5
  • 4
  • +5
6 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>wholesalers which service more than one retailer, but only if one of the retailers has a retailer_id = 337.

Give this a whirl..
SELECT wholesaler_id, COUNT(retailer_id) as retailer_count
FROM wholesalers_and_retailers
WHERE wholesaler_id IN (
   SELECT DISTINCT wholesaler_id
   FROM wholesalers_and_retailers
   WHERE retailer_id <> 337)
GROUP BY wholesaler_id
ORDER BY wholesaler_id

Open in new window

0
 
Gustav BrockCIOCommented:
You can do something like this:

Select
  wholesaler_id,
  Sum(Abs(retailer_id = 337)) As CountRetailer337,
  Count(*) As CountRetailers
From
   wholesalers_and_retailers
Having
  Sum(Abs(retailer_id = 337)) = 1 And Count(*) > 1
Group By
  wholesaler_id

/gustav
0
 
unknown_routineCommented:
Select   wholesaler_id , count(retailer_id)
from
wholesalers_and_retailers
having count(retailer_id)>1 and retailer_id<>337
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
try this query


select wr.wholesaler_id, wr.retailer_id
from wholesalers_and_retailers as wr
inner join
(select wr1.wholesaler_id from wholesalers_and_retailers as wr1
Where wr1.retailer_id=337)
as w
on wr.wholesaler_id=w.wholesaler_id
0
 
dannygonzalez09Commented:
something like this

SELECT S.* FROM Wholesale_retail S
INNER JOIN
(
SELECT * FROM Wholesale_retail S1
WHERE Retailer_id = 337
)W
ON S.WholeSaler_id = W.WholeSaler_id and S.Retailer_id = W.Retailer_id
0
 
unknown_routineCommented:
Select   wholesaler_id , count(retailer_id)
from
wholesalers_and_retailers
Where Retailer_id<>337
having count(retailer_id)>1
0
 
dannygonzalez09Commented:
Sry you don't need a join on the retailer id... chk this

SELECT S.* FROM Wholesale_retail S
INNER JOIN
(
SELECT * FROM Wholesale_retail S1
WHERE Retailer_id = 337
)W
ON S.WholeSaler_id = W.WholeSaler_id
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Slight change to my original post, forgot the HAVING, although I suspect multiple comments above will also work.
SELECT wholesaler_id, COUNT(retailer_id) as retailer_count
FROM wholesalers_and_retailers
WHERE wholesaler_id IN (
   SELECT DISTINCT wholesaler_id
   FROM wholesalers_and_retailers
   WHERE retailer_id <> 337)
GROUP BY wholesaler_id
HAVING COUNT(retailer_id) > 1
ORDER BY wholesaler_id

Open in new window

0
 
mike2401Author Commented:
So far, I've only looked at the last post (jimhorn's) because it was the easiest to read.  However, it appears not to be doing the following:

"I'm trying to identify wholesalers which service more than one retailer, but only if one of the retailers has a retailer_id = 337."

For the first wholesaler I checked, the count was three, and there were indeed 3 retailers associated with that wholesaler, but none were for retailer 337.

Basically, I'm trying to get 4 retailers, the 337 and the 3 others which are not 337.  (sorry if I'm not expressing that well).

Mike
0
 
Rey Obrero (Capricorn1)Commented:
i'll repost my query, try this



select wr.wholesaler_id, wr.retailer_id
from wholesalers_and_retailers as wr
inner join
(select wr1.wholesaler_id from wholesalers_and_retailers as wr1
Where wr1.retailer_id=337)
as w
on wr.wholesaler_id=w.wholesaler_id



.
0
 
awking00Commented:
Can you provide a description of your table, some sample data and your expected output?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Oh I see what you mean.  Group by warehouse, and results must have least one retailer = 337 and at least one retailer <> 337.

Copy-paste the below into your SSMS, execute to verify it meets your requirements, then modify to meet your needs.
CREATE TABLE #wholesalers_and_retailers (wholesaler_id int, retailer_id int) 

INSERT INTO #wholesalers_and_retailers (wholesaler_id, retailer_id)
VALUES 
   (1,1), (1,2),       -- not returned, no 337
   (2, 337), (2, 543), -- so this one would be returned
   (3, 337), (3, 337), -- not returned, no non-337
   (4, 337), (4, 123), (4, 5), (4, 42) -- so this one would be returned

SELECT * FROM #wholesalers_and_retailers

SELECT wr.wholesaler_id, COUNT(wr.retailer_id) as retailer_count
FROM #wholesalers_and_retailers wr
   JOIN  (
      -- All wholesalers with a 337 retailer
      SELECT DISTINCT wholesaler_id
      FROM #wholesalers_and_retailers
      WHERE retailer_id = 337) wr337 ON wr.wholesaler_id = wr337.wholesaler_id
   JOIN (
      -- All wholesalers with a non-337 retailer
      SELECT DISTINCT wholesaler_id
      FROM #wholesalers_and_retailers
      WHERE retailer_id <> 337) wrnot337 ON wr.wholesaler_id = wrnot337.wholesaler_id
GROUP BY wr.wholesaler_id
HAVING COUNT(wr.retailer_id) > 1
ORDER BY wr.wholesaler_id

Open in new window

0
 
Scott PletcherSenior DBACommented:
I suggest avoiding any type of self-join / subquery join for performance reasons:

SELECT wholesaler_id
FROM wholesalers_and_retailers
GROUP BY wholesaler_id
HAVING
    MAX(IIF(retailer_id = 337, 1, 0)) = 1 AND
    MAX(IIF(retailer_id <> 337, 1, 0)) = 1
ORDER BY wholesaler_id
0
 
mike2401Author Commented:
Genius capricorn1 !!!  Absolutely perfect !!!

I'm going to did out my SQL book so I can understand how you did it all in 1 statement.  Your solution is much better than what I came up with (copying to a temp table, then doing sql against that).

Thank you!!!
0
 
mike2401Author Commented:
I'm not sure what the etiquette is for awarding points.

I'm not smart enough to know if the other answers will work, though I think some of the answers above only would give wholesalers who do not have a retailer = 337.

I will split points because I sincerely appreciate everyone who took the time to help.

Hoping I don't offend anyone  . . .

Here it goes:

Mike
0
 
Rey Obrero (Capricorn1)Commented:
try copy and paste the queries posted and award points to those that will work.
0
 
mike2401Author Commented:
Wow, this was really hard to split up points.  

I certainly have some SQL studying to do from these SQL Ninja's!!  I've never heard of some of these techniques!

Have a great weekend everyone!

Mike
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
A split generally works.

Offhand, explain for us how Capricorn1's SQL meets your needs, as I don't see it working if the requirement is to identify wholesalers that have at least one 337 relailer AND one non-337 retailer.

>Should I first query all records which have a retailer_id= 337, and send the results to a temp table?  Then somehow join that list of wholesalers to the main table, but only if the retailer_id is NOT 337?

Based on the above statement it seems that that's what the requirements are.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Scott - Nice use of HAVING MAX(...   I'll have to remember that trick.
0
 
Scott PletcherSenior DBACommented:
Thanks jim!  *25* points worth of nice :-) .

Btw, if you want to list the entire row for those wholesalers, you will have to join back to the original table:

SELECT wr.*
FROM wholesalers_and_retailers wr
INNER JOIN (
    SELECT wholesaler_id
    FROM wholesalers_and_retailers
    GROUP BY wholesaler_id
    HAVING
        MAX(IIF(retailer_id = 337, 1, 0)) = 1 AND
        MAX(IIF(retailer_id <> 337, 1, 0)) = 1
) AS w ON
    w.wholesaler_id = wr.wholesaler_id
ORDER BY wr.wholesaler_id, wr.retailer_id
0
 
Rey Obrero (Capricorn1)Commented:
<Offhand, explain for us how Capricorn1's SQL meets your needs, as I don't see it working if the requirement is to identify wholesalers that have at least one 337 relailer AND one non-337 retailer.>

that is very simple

this part

select wr.wholesaler_id, wr.retailer_id
from wholesalers_and_retailers as wr

will give all the records

this part

select wr1.wholesaler_id from wholesalers_and_retailers as wr1
Where wr1.retailer_id=337

will give you all the wholesaler_id that serve retailer_id=337

this is like just creating a query and saving it and using the saved query and the table in another query..
0
 
mike2401Author Commented:
I changed the table and field names when I posted my question to make it more general and intuitive to answer.

Here's the actual code with the actual table/field names:

Table= [Magnet Store]
The "wholesaler" is: [Magnet Store].[mag store id]
The "retailer" is: [Magnet Store].[Curtis WAID]

And, it's not 337 but rather:  [Magnet Store].[Curtis WAID] ='504990'

Here's what I did myself (as a two step process):

Step one:
      
SELECT * into temp1 FROM [Magnet Store]
where [Magnet Store].[Curtis WAID] ='504990'

Step two:

select [Magnet Store].[mag store id] ,  [Magnet Store].[Curtis WAID] from temp1, [Magnet Store] where  [Magnet Store].[mag store id]=temp1.[mag store id] and  [Magnet Store].[Curtis WAID] <>'504990'  order by [Magnet Store].[mag store id];

This worked.

I modified capricorn1's sql to my table/field names:

select wr.[mag store id], wr.[Curtis WAID]
from  [Magnet Store] as wr
inner join
(select wr1.[mag store id] from  [Magnet Store] as wr1
Where wr1.[Curtis WAID]='504990')
as w
on wr.[mag store id]=w.[mag store id]  ;

I got the same results.

Mike
0
 
mike2401Author Commented:
Sorry ScottPletcher for only 25 points of "nice".

It was so far over my head, I had no idea if it would work .

I knew I would screw up the allocation.

I was anxious to close the call before any other people offered suggestions.

Mike
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 5
  • 4
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now