Left outer join followed by inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Left outer join followed by inner join

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2005-09-13 : 16:47:41

I have 3 tables. One for my customer, one for the emails I'm sending to my customers and one tracking the type of emails I've sent to them

Customer
customer_id
email

Email_sent
customer_id
email_sent_template_id

Email_sent_template
email_sent_template_id
business_rule_id

What I want is all my customers who did not receive a specific business rule, 1 for this example. Logically I would do this select:

select
c.customer_id
,c.email
from
customer c
left outer join email_sent es
on es.customer_id = c.customer_id
inner join email_sent_template est
on est.email_sent_template_id = es.email_sent_template_id
and business_rule_id = 1
where
es.email_sent_id is null

This select statement is not working because of the inner join between email_sent_template and email_sent, it completly ignore the left outer join on email_sent and customer. Anyone can see what I want to do. The workaround is to put a left outer join on email_sent_template and then doing a group by having max(business_rule_id) is null but if its possible I'd like to avoid doing that. These tables will have millions of row, so if I can have inner join instead of left outer join I'd appreciate.

Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-13 : 18:25:38

[code]select
c.customer_id
,c.email
from
customer c
left outer join
(
email_sent es
inner join email_sent_template est
on est.email_sent_template_id = es.email_sent_template_id
and business_rule_id = 1
)
on es.customer_id = c.customer_id
where
es.email_sent_id is null[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources