How to List Left-Outer Join Data

I have a left-outer join between a table of Sales Opportunities and a table
with Activities associated with those Opportunities.
On one of my report tables I need to filter out all completed activities and
then list those Opportunities that do not have an activity left (eg no open
activities are scheduled).
I'm a bit clueless on how to establish the 'null activity record' filter.
I have three other report tables on this report, so I can't filter out
records on the data tab -- I need to create a filter on the report table.
I appreciate any help!uh cant you just put the 'null activity record' in the where clause?
where [activity record] is null
Cindy wrote:
> I have a left-outer join between a table of Sales Opportunities and a table
> with Activities associated with those Opportunities.
> On one of my report tables I need to filter out all completed activities and
> then list those Opportunities that do not have an activity left (eg no open
> activities are scheduled).
> I'm a bit clueless on how to establish the 'null activity record' filter.
> I have three other report tables on this report, so I can't filter out
> records on the data tab -- I need to create a filter on the report table.
> I appreciate any help!|||try something like this:
Create table [Sales Opportunities]
(pk int not null identity constraint sopk primary key, opportunityname
varchar(50))
GO
Create table [Activities]
(pk int not null references [Sales Opportunities](pk), activities
varchar(50))
GO
insert into[Sales Opportunities] (opportunityname) values('opportunity with
an activity')
GO
insert into[Sales Opportunities] (opportunityname) values('opportunity
without an activity')
GO
insert into Activities values(1,'activity1')
GO
select * from [Sales Opportunities] left join Activities
on [Sales Opportunities].pk= Activities.pk
where Activities.pk is null
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:OD3h46l6GHA.1484@.TK2MSFTNGP05.phx.gbl...
>I have a left-outer join between a table of Sales Opportunities and a table
>with Activities associated with those Opportunities.
> On one of my report tables I need to filter out all completed activities
> and then list those Opportunities that do not have an activity left (eg no
> open activities are scheduled).
> I'm a bit clueless on how to establish the 'null activity record' filter.
> I have three other report tables on this report, so I can't filter out
> records on the data tab -- I need to create a filter on the report table.
> I appreciate any help!
>|||Hello Cindy,
You need to create a dataset which include these two tables and put the
left join information in it.
Then, you could use the approach Kaisa provided in your post: "Exclude Null
Dates"
This will be useful for your scenario.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I think there may be a flaw in my thinking on this one...
In my data source, there may be closed activity records that exist and are
connected to the opportunity. Is it possible to filter the opportunities to
cases where there are no open activity records?
I'm thinking I need to filter out the closed activities as part of the SQL
statement so that the left outer join has a chance to kick in and have no
activity records.|||Hello Cindy,
Would you please post your Table design of the Opportunities and Activity
table so that we could provide more detailed suggestion on this issue?
Also, some sample data is appreciated.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.