Create query with multiple criteria from a many to many relationship 2

[dmuroff]

Alright I've been picking my brain for the past hour trying to figure this one out...

I have 3 tables...
1) Retailers-RetailerID, RetailerName, address etc. (500 entries)
2) Distributors- DistributorID, DistName (10 entries)
3) Uses- RetailerID, DistributorID

Sample data is as follows for Distribution table:
RetailerID DistributorID
1 1
1 3
2 1
2 6
3 4
3 5
4 8

I am trying to create a query which will give return retailer information for those retailer who use multiple distributors.

I have tried the following but I have been unsuccessful:

SELECT Uses.DistributorID, Uses.RetailerID
FROM Uses
WHERE ((Uses.DistributorID)=1 And (Uses.DistributorID)=2);

Is this possible to accomplish with the way I designed this database? Thanks!

 

[lespaul]

Try this:

SELECT Uses.DistributorID, Uses.RetailerID
FROM Uses
HAVING Count(DistributorID) > 1

Leslie

 

[dmuroff]

That works perfectly.
But is there a way to choose to see ONLY the retailer information for those retailers that use Distributor 1 AND 2?

 

[lespaul]

SELECT Uses.DistributorID, Uses.RetailerID
FROM Uses
WHERE DistributorID IN (1, 2)
HAVING Count(DistributorID) > 1

Leslie

 

[dmuroff]

Awesome works great! Thanks Leslie!

Just 1 last thing: I want to display all the retailers that use distributor 1 and anyother distributor
ex: (1 and 2) or (1 and 3) or (1 and 4) etc. I used the query builder and it came out with this code:

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,2) Or (Uses.DistributorID) In (1,3) Or (Uses.DistributorID) In (1,4) Or (Uses.DistributorID) In (1,5) Or (Uses.DistributorID) In (1,6) Or (Uses.DistributorID) In (1,7)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));

This does not appear to be doing anything differently than the code in your last post. Any suggestions?

 

[lespaul]

If my logic is working this early in the morning this might work:

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID) = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));

So it's only going to select Retailers that use Distributor1 but have a count of more than one distributor.

Is that right?

Leslie

 

[dmuroff]

Good morning Leslie,

Yes you are right. That is exactly what I want the query to display.
I tried your idea and it did not display anything. No records are being displayed and there is data in the database in which this query is true. Can you think of anything else?

If you look at my post from yesterday with all the 'or' criteria it is not taking any of them into account and the query is bascially being treated as if they were not there at all. hence it is showing all those retailers with >1 distributors.

Thanks!

 

[lespaul]

Ok, what if you just run :

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address

does that return the correct information? Do the counts look correct?



Leslie

 

[dmuroff]

That ends up showing ALL records that use DistributorID 1.

I'm looking for DistributorID AND anyother distributor.

If i do..

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,2) Or (Uses.DistributorID) In (1,3)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));

It works fine but obviously it only displays those that use (1 AND 2) or (1 AND 3).
Then if I add all of them up to (1 AND 10) the WHERE statement is as good as useless because it doesn't filter any records out.

Please let me know if that made any sense. LOL.Thanks!

 

[dmuroff]

Alright I took the suckers way out..

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,2)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));
UNION SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,3)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));
UNION SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,4)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));
UNION SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,5)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));
UNION SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,6)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));
UNION SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE (((Uses.DistributorID) In (1,7)))
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));

Ain't pretty but it works!
Can you think of anything?

 

[lespaul]

So, the query without the having:

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address

That ends up showing ALL records that use DistributorID 1 and counts the number of distributors that retailer uses:

WalMart 5
Sam's Club 3
Costco 2
Joe's Market 1

So all of these distributors use distributor 1.

Walmart uses 4 other distributors
Sam's club uses 2 other distributors
and Costco uses 1 other distributor

Joe's Market uses no other distributor, so when we add the having clause:

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.DistributorID) > 1

Should eliminate Joe's Market from the result and leaves the rest.

Is that not what occurs?

Leslie

 

[dmuroff]

Leslie..
This is not what happens.

In this query

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address

It shows all those retailers that use distributor 1 and the Count is ALWAYS 1.

Maybe this occurs because of the way I set up my table
The 'Uses' table looks like this:
Retailer Distributor
Joe Walmart
Joe Costco
Joe Sam's Club
Mary Walmart
Mary Sam's Club
Elliot Sam's Club
(The actual data uses the ID's but you get the idea)

This code:

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.DistributorID) > 1

Shows absolutely no records.

 

[lespaul]

What if you change it to:

SELECT Uses.RetailerID, Retailers.Address, Count(*) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.DistributorID) > 1

Leslie

 

[dmuroff]

Samething
Doesn't Display anything.

 

[dmuroff]

Oops sorry.
It displays All those that use Distributor 1. Count remains at 1 ALWAYS.

 

[lespaul]

maybe......this:

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.DistributorID) > 1

Leslie

 

[dmuroff]

Nope no luck
I'll try to work my way around it.
Thanks for the try Leslie.
Appreciate it!

Dan

 

[lespaul]

well, i didn't write it the way I thought I did!!! my bad...try once more!

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.RetailerID) > 1

Leslie

 

[PHV]

And what about this ?
SELECT Uses.RetailerID, Retailers.Address, Count(Uses.DistributorID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.RetailerID IN (SELECT RetailerID FROM Uses WHERE DistributorID=1)
GROUP BY Uses.RetailerID, Retailers.Address
HAVING (((Count(Uses.DistributorID))>1));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dmuroff]

PHV... I think that might have done it.
I'll have to look at the data to make sure all the related records are there but I think this is it!

Thanks for the help!