SQL "All" Functionality?
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.Numbe开发者_如何学编程r fields is null)
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)
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.
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
精彩评论