MySQL COUNT DISTINCT to determine attribut

Database
N/A

MySQL COUNT DISTINCT to determine attribut

Exercise 2: (a) Use COUNT and DISTINCT to determine how many distinct stores there are in the
strinfo, store_msa, skstinfo, and trnsact tables.
(b) Which stores are common to all four tables, or unique to specific tables?

 

I know the answer to (a) but can't figure out the most appropriate to (b).

Can someone help me with it please?Capture.JPG

4 REPLIES
N/A

Re: MySQL COUNT DISTINCT to determine attribut

Common to all tables:

select strinfo.store
from strinfo join store_msa
  on strinfo.store =  store_msa.store
join (select distinct store from skstinfo) as skstinfo
  on strinfo.store =  skstinfo.store
join (select distinct store from trnsact) as trnsact
  on strinfo.store =  trnsact.store

or

select store
from strinfo
where exists
 ( select * from store_msa where strinfo.store =  store_msa.store )
and exists
 ( select * from skstinfo  where strinfo.store =  skstinfo.store )
and exists
 ( select * from trnsact   where strinfo.store =  trnsact.store )

The Exists can be easily changed to find unique to specific tables:

select store
from strinfo
where not exists
 ( select * from store_msa where strinfo.store =  store_msa.store )
and not exists
 ( select * from skstinfo  where strinfo.store =  skstinfo.store )
and not exists
 ( select * from trnsact   where strinfo.store =  trnsact.store )

Other solutions might be based on SET-operators, INTERSECT and EXCEPT.

 

N/A

Re: MySQL COUNT DISTINCT to determine attribut

I'm familier only with the first solution. The result is: 327 rows total.

Does it mean that 327 stores are common to all four tables, or unique to specific tables?

N/A

Re: MySQL COUNT DISTINCT to determine attribut

Of course those 327 stores are common, Inner Joins only return matching data.

N/A

Re: MySQL COUNT DISTINCT to determine attribut

Thank you.