July 29, 2011 at 9:23 am
This seems like an easy query, yet all my attempts have failed to find the answer.
Mainly I have 2 tables: TableA and TableB.
A record in TableA can have several related values in TableB. What I'm trying to do is select TableA records based on the 1 to several values that may exist in TableB. The values in TableB I'm trying to evaluate are date values.
For example:
TableA.ID TableB.Date
----------------------------------
1 1/1/2011
1 3/1/2011
1 Null
2 1/10/2011
2 2/3/2011
3 2/12/2011
3 3/3/2011
3 Null
In the example above, what I'd like to do is select only those records in TableA that do not have a Null value in any of the corresponding records in TableB.
I have difficulty evaluating using GroupBy function because I cannot find a way to make the date an aggregate value that I can evaluate.
July 29, 2011 at 9:30 am
TTboy (7/29/2011)
This seems like an easy query, yet all my attempts have failed to find the answer.Mainly I have 2 tables: TableA and TableB.
A record in TableA can have several related values in TableB. What I'm trying to do is select TableA records based on the 1 to several values that may exist in TableB. The values in TableB I'm trying to evaluate are date values.
For example:
TableA.ID TableB.Date
----------------------------------
1 1/1/2011
1 3/1/2011
1 Null
2 1/10/2011
2 2/3/2011
3 2/12/2011
3 3/3/2011
3 Null
In the example above, what I'd like to do is select only those records in TableA that do not have a Null value in any of the corresponding records in TableB.
I have difficulty evaluating using GroupBy function because I cannot find a way to make the date an aggregate value that I can evaluate.
Something like this?
select ... from TableA a
left join TableB b on a.ID = b.ID and b.Date is null
where b.ID is Null
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 29, 2011 at 9:39 am
Using NOT EXISTS in your WHERE clause might work...
SELECT ...
FROM A
WHERE NOT EXISTS (SELECT * FROM B WHERE A.ID=B.ID and B.DATE IS NULL)
jg
July 29, 2011 at 11:21 am
Yes, That works.
Thank You.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply