Solved

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

Posted on 2013-11-08
23
244 Views
Last Modified: 2013-11-08
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
Comment
Question by:mike2401
  • 6
  • 5
  • 4
  • +5
23 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39633825
>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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 25 total points
ID: 39633826
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
 
LVL 15

Expert Comment

by:unknown_routine
ID: 39633851
Select   wholesaler_id , count(retailer_id)
from
wholesalers_and_retailers
having count(retailer_id)>1 and retailer_id<>337
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39633855
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
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 50 total points
ID: 39633856
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
 
LVL 15

Expert Comment

by:unknown_routine
ID: 39633867
Select   wholesaler_id , count(retailer_id)
from
wholesalers_and_retailers
Where Retailer_id<>337
having count(retailer_id)>1
0
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 50 total points
ID: 39633874
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39633925
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
 

Author Comment

by:mike2401
ID: 39634489
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 350 total points
ID: 39634500
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
 
LVL 31

Expert Comment

by:awking00
ID: 39634539
Can you provide a description of your table, some sample data and your expected output?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 39634581
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 25 total points
ID: 39634588
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
 

Author Comment

by:mike2401
ID: 39634593
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
 

Author Comment

by:mike2401
ID: 39634601
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39634606
try copy and paste the queries posted and award points to those that will work.
0
 

Author Closing Comment

by:mike2401
ID: 39634621
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39634629
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39634633
Scott - Nice use of HAVING MAX(...   I'll have to remember that trick.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39634661
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39634673
<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
 

Author Comment

by:mike2401
ID: 39634680
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
 

Author Comment

by:mike2401
ID: 39634707
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now