Comparing tables

[pogo1]

So I have these tables:

I want to find the name of every store that has T-shirts of all sizes. Any suggestions?

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

every store that has T-shirts of all sizes

Is that stores with all possible sizes of different t-shirts or
All possible t-shirts of all possible sizes
or at least one type of t-shirt with all possible sizes?

 

[pogo1]

PHV:
Well I haven't gotten as far as a single working SQL statement yet but the method I want to use is counting the number of unique sizes of Tshirts for every store and comparing with the count of rows in the table "Size". This is where I'm at currently (obviosly not complete):

SELECT s.name
FROM STORE AS s
WHERE EXISTS(
SELECT COUNT(*)
FROM(
SELECT DISTINCT t.size
FROM StoreTshirt AS st, Tshirt AS t
WHERE st.tshirt=t.tid
AND s.sid=st.store <----- This is not allowed
)
)

MajP
Sorry to be inprecise. I want:
Name of stores that have at least one T-shirt of every size.
So if the table "Size" has three rows: "Large", "Medium", "Small", then only stores which has Tshirts of all of those three sizes should appear in the result.

 

[pogo1]

Actually, disregard the previously posted SQL code. This is more to the point:
Below is the part where I try to find out how many different sizes of tshirts are in every store.

SELECT s.name, COUNT(t.size) AS sizes
FROM Store AS s, StoreTshirt AS st, Tshirt AS s
WHERE s.sid=st.store AND st.tshirt=t.tid
GROUP BY s.namn, t.size

But what it gives me is actually the number of tshirts, grouped by sizes in different stores. So for the first line i need something like:

SELECT s.name, COUNT( DISTINCT t.size ) AS sizes

But COUNT(DISTINCT x) is not allowed in MS Access. How can I express that in a different way?

 

[PHV]

Something like this (typed, untested) ?

SELECT S.name
FROM Store S INNER JOIN (
 SELECT DISTINCT ST.store,T.size FROM StoreTshirt ST INNER JOIN Tshirt T ON ST.tshirt=T.tid
) D ON S.sid=D.store
GROUP BY S.name
HAVING COUNT(*)=(SELECT COUNT(*) FROM Size)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pogo1]

Thanks that helps alot!

 

[pogo1]

Came up with another solution that seem to work. Maybe not as elegant.

SELECT name
FROM(
SELECT name, COUNT(size) AS sizes
FROM(
SELECT s.name, t.size
FROM Store AS s, StoreTshirt AS st, Tshirt AS t
WHERE s.sid=st.stor AND st.tshirt=t.tid
GROUP BY s.name, t.size
)
GROUP BY name
)
WHERE sizes = (
SELECT COUNT(name)
FROM Size
)