EXPERT RESPONSE
Seems pretty straightforward. Try this:
select T2.Num
from T1
inner
join T2
on T1.Num = T2.Num
group
by T2.Num
having count(distinct T2.Val) = 1
and min(T2.Val) = 'S'
Since each row of T1 can join to more than one row of T2,
a GROUP BY on T2.Num is required. The HAVING clause
ensures that any T2.num is returned only when there's
exactly one distinct T2.Val value in its group, and this value is 'S'.
The MIN function is used, but it could also have been MAX, since
there would be only one distinct value.
Note that a WHERE clause
to restrict T2 rows to those containing 'S' would be wrong,
unless accompanied by a NOT EXISTS correlated subquery to check for
any other rows with any other value besides 'S' -- but this would
be needlessly complex, so I won't show it.
UPDATE: Reader B. Humphreys emailed me with a
concern about the solution above:
My understanding was that "Count(FieldName)" returned a
count of NON-NULL values for the captioned field while "Count(*)"
returned a count of all records (NULL or not). If that's the case, will this actually work properly or will
it return TRUE even though the field "T2.Val" contains NULLs in
addition to one other non-NULL value?
An excellent observation. One way around this is
to replace any NULLS with an empty string, and if there are only
NULLs, then the MIN() condition would not be 'S':
select
...
having count(distinct coalesce(T2.Val,'')) = 1
and min(T2.Val) = 'S'
Mr. Humphreys suggested:
select
...
having count(T2.Val) = count(*)
and min(T2.Val) = 'S'
and max(T2.Val) = 'S'
This works perfectly, even in Microsoft Access
(which does not support COUNT DISTINCT, at least
not in the version I'm using). Thanks, B. Humphreys!
|
