Returning specific rows from Many-many relationships (1 Viewer)

theferd

Registered User.
Local time
Today, 10:13
Joined
Dec 23, 2019
Messages
42
Hi All,

I am having difficulties developing a query to return rows that include all of the requested criteria in a single field. Because of the nature of the data in my database I will make a short example to explain my situation.

I have a clothing catalog with shirts. They come in sizes ranging from sm to XL, though not all shirts include every size. To assign which sizes apply to which shirts I have set up the appropriate association table (many-to-many).

My issue is that I cannot construct a query that returns all shirts with the explicit sizes I request. I want shirts that have ONLY medium AND large, but if I try putting that into the criteria it won't return anything since one cell contains one size.

How would I go about doing this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:13
Joined
Oct 29, 2018
Messages
21,357
Hi. Can you post your table structure and the SQL statement you're trying to use for your query?
 

plog

Banishment Pending
Local time
Today, 09:13
Joined
May 11, 2011
Messages
11,611
You didn't provide table and field names so I will make my own according to what you should have.

Code:
SELECT Shirt_ID
FROM tblSizes
GROUP BY Shirt_ID
HAVING COUNT(Shirt_ID)=2 AND MAX(Size)="medium" AND MIN(Size)="large"

The magic is all in the HAVING clause--you want all Shirt_IDs in the Sizes table that have just 2 total records; the largest of those 2 values should be "medium"; and the smallest of those 2 values should be "large".
 

theferd

Registered User.
Local time
Today, 10:13
Joined
Dec 23, 2019
Messages
42
I uploaded a snippet of my database for reference. I was attempting to use
WHERE (((tblSizes.Sizes)="m" And (tblSizes.Sizes)="l")); to see if it worked but I already knew from the start it wouldn't. I am somewhat new to VBA so if there are more useful arguments then I would like to know them.

The magic is all in the HAVING clause--you want all Shirt_IDs in the Sizes table that have just 2 total records; the largest of those 2 values should be "medium"; and the smallest of those 2 values should be "large".

I think this might work? Later down the line I want to construct a form using either a combo box or check boxes to search for 1-4 sizes in the same manner.
 

Attachments

  • shirtcatalog.accdb
    792 KB · Views: 91

plog

Banishment Pending
Local time
Today, 09:13
Joined
May 11, 2011
Messages
11,611
You've over-normalized your database: Tables with only 1 real field of data (autonumber primary keys are not real data) do not need to exist. That means you really only need a table like tblshirtsizeassociation that has both shirts and sizes; unless of course you have more actual fields in tblShirts and tblSizes and are giving us a striped down version.

Also, my initial approach was correct. Convert it to use your tables/fields and it should work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:13
Joined
Oct 29, 2018
Messages
21,357
Hi. I haven't downloaded your file, but another possible approach is to use subqueries for each size. Just thinking out loud...
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:13
Joined
Sep 21, 2011
Messages
14,042
How can a size be "m" AND "l" because you are comparing one record at a time.

In that example you would want OR not AND.

I uploaded a snippet of my database for reference. I was attempting to use
WHERE (((tblSizes.Sizes)="m" And (tblSizes.Sizes)="l")); to see if it worked but I already knew from the start it wouldn't. I am somewhat new to VBA so if there are more useful arguments then I would like to know them.



I think this might work? Later down the line I want to construct a form using either a combo box or check boxes to search for 1-4 sizes in the same manner.
 

theferd

Registered User.
Local time
Today, 10:13
Joined
Dec 23, 2019
Messages
42
Code:
SELECT tblShirts.Shirt, tblSizes.Sizes
FROM tblSizes INNER JOIN (tblShirts INNER JOIN tblshirtsizeassociation ON tblShirts.ID = tblshirtsizeassociation.ShirtID) ON tblSizes.ID = tblshirtsizeassociation.SizeID
GROUP BY tblShirts.Shirt, tblSizes.Sizes
HAVING ((Count(tblShirts.Shirt)=2) AND (Max(tblSizes.Sizes)="m") AND (Min(tblSizes.Sizes)="l"));

I redressed the SQL to fit the names of my tables but for some reason it isn't returning anything? Mind helping with some bug fixing?

Also your guess is correct, there are other fields in shirts not included in the file I uploaded
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 28, 2001
Messages
26,999
theferd, you should read Gasman's comments.

This sub-clause: (Max(tblSizes.Sizes)="m") AND (Min(tblSizes.Sizes)="l"))

should read: (Max(tblSizes.Sizes)="m") OR (Min(tblSizes.Sizes)="l"))
 

theferd

Registered User.
Local time
Today, 10:13
Joined
Dec 23, 2019
Messages
42
The issue with the OR statement is that it returns everything that has L or M, meaning a shirt that only has small and medium will appear in this case. I only want shirts that have both L and M to appear.
 

plog

Banishment Pending
Local time
Today, 09:13
Joined
May 11, 2011
Messages
11,611
Your query should just be on the one table with both sizes and shirts, I believe it is tblshirtsizeassociation. CHange my initial query to use just that table and it will return the ShirtID of all the shirts that meet your criteria.

THen, if you need other fields (size and shirt) you can use that as a subquery into the other tables. The query I posted should only contain 1 table.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:13
Joined
Sep 21, 2011
Messages
14,042
Here is what I managed, but I used the size ids

Code:
SELECT tblShirts.Shirt, Count(tblshirtsizeassociation.SizeID) AS CountOfSizeID
FROM tblSizes INNER JOIN (tblShirts INNER JOIN tblshirtsizeassociation ON tblShirts.ID = tblshirtsizeassociation.ShirtID) ON tblSizes.ID = tblshirtsizeassociation.SizeID
GROUP BY tblShirts.Shirt
HAVING (((Count(tblshirtsizeassociation.SizeID))=2) AND ((Min(tblshirtsizeassociation.SizeID))=2) AND ((Max(tblshirtsizeassociation.SizeID))=3));

Seems Plog has confirmed I was heading in the right direction?

Edit:

This is using the actual sizes?

Code:
SELECT tblShirts.Shirt, Count(tblshirtsizeassociation.SizeID) AS CountOfSizeID
FROM tblSizes INNER JOIN (tblShirts INNER JOIN tblshirtsizeassociation ON tblShirts.ID = tblshirtsizeassociation.ShirtID) ON tblSizes.ID = tblshirtsizeassociation.SizeID
GROUP BY tblShirts.Shirt
HAVING (((Count(tblshirtsizeassociation.SizeID))=2) AND ((Max(tblSizes.Sizes))="m") AND ((Min(tblSizes.Sizes))="l"));

HTH
 
Last edited:

theferd

Registered User.
Local time
Today, 10:13
Joined
Dec 23, 2019
Messages
42
That did the trick, thanks.

Though I got to thinking that if I were to require sizes that weren't sequential, say small and large, or more than two sizes, this may run into issue.

I always considered making two sub queries, one for each size and then a main query to compare the common records, but I was wary of having to create a sub query for every size due to the amount of visual clutter that would cause. However, I think this may be the most robust method. Sorry for the trouble!
 
Last edited:

Users who are viewing this thread

Top Bottom