Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-08
23
Medium Priority
?
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +5
23 Comments
 
LVL 66

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 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 200 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 200 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 66

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1400 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 32

Expert Comment

by:awking00
ID: 39634539
Can you provide a description of your table, some sample data and your expected output?
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 66

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 66

Expert Comment

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

Expert Comment

by:Scott Pletcher
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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