SQL "All" Functionality?

Question

This is probably a really easy question, it's just very hard to google a word like "All".

SELECT a.Id, Max(b.Number)
FROM Table1 a
JOIN Table2 b
    ON a.FK = b.Id
GROUP BY a.Id

But I want to add a where clause that specifies that all b.Id's linked to an a.FK must have values. So basically I don't want to select the a.Id grouping of b.Id's where any of those b.Id's are null. Hope I made that clear, let me know if I need to elaborate. Thanks.

Edit - For some clarification (Changed the query above as well):

Table1
Id, FK
1   1
1   2
2   3
3   4
3   5
3   6

Table 2
Id   Number
1    1
2    NULL
3    10
4    20
5    30
6    40

I would want my query to show:

a.Id   Max Number
2      10
3      40

(Notice that a.Id = 1 doesn't show up because one of the b.Number fields is null)

Answer 1

SELECT a.Id, Max(b.Id)
FROM Table1 a
JOIN Table2 b
ON a.FK = b.Id
WHERE b.Id is NOT NULL
GROUP BY a.Id

Answer 2

Okay, you are asking a totally different question from the one I thought you were. I'm replacing my answer.

The way I would handle this is to join a to b twice -- once to get all matching rows in b, and a second join to search for rows in b where Number is null. If no such row exists, then we know they're all non-null.

SELECT a.Id, Max(b1.Number)
FROM Table1 a
JOIN Table2 b1 ON a.FK = b1.Id
LEFT OUTER JOIN Table2 b2 ON a.FK = b2.Id AND b2.Number IS NULL
WHERE b2.Id IS NULL
GROUP BY a.Id

b2.Id will be null only if no row is found where b2.Number is null.

Answer 3

select t1.Id, max(Number) as [Max Number]
from Table1 t1
left join Table2 t2 ON t1.FK=t2.Id and t2.Number is not null
group by t1.Id
having count(distinct t1.FK) = count(distinct t2.Id)