Changing Multiple *= on same 2 tables to outer joi

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 2005 Forums

Transact-SQL (2005)

Changing Multiple *= on same 2 tables to outer joi

Author

Topic

chrpeter
Starting Member

31 Posts

Posted - 2008-03-03 : 13:32:44

I'm trying to convert this statement to use left outer joins so it will be 2005 compatible.

select
convert(varchar(8),a.order_no),
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
0,
a.seq_descript
from fm_orderline a, orders b, ord_list c, fm_ordhist d
where c.order_no = a.order_no
and c.order_ext = a.order_ext
and c.line_no = a.line_no
and c.order_no *= d.order_no
and c.order_ext *= d.order_ext
and c.line_no *= d.line_no
and b.order_no = c.order_no
and b.ext = c.order_ext
and d.line_no <> 999
and c.status = 'T'
group by
d.order_no,
convert(varchar(8),a.order_no),
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
a.seq_descript
having d.order_no is null

I've tried this:
select
convert(varchar(8),a.order_no) as order_no,
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
0,
a.seq_descript
from fm_orderline a, orders b,
ord_list c
left outer join fm_ordhist AS d on (c.order_no = d.order_no)
left outer join fm_ordhist AS d2 ON (c.order_ext = d2.order_ext)
left outer join fm_ordhist AS d3 ON (c.line_no = d3.line_no)
where c.order_no = a.order_no
and c.order_ext = a.order_ext
and c.line_no = a.line_no
and b.order_no = c.order_no
and b.ext = c.order_ext
and d.line_no <> 999
and c.status = 'T'

And various derivations of it(adding d2.line_no <> 999 and d3.line_no <> 999 to the where clause.) But it came up with different results than what I received when it was a *= query.

Can any gurus on here help me out?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-03 : 14:36:35

you should not be joining 3 copies of the same fm_ordhist table because you need to join on one more than column; just put it all into your join expression:


left outer join fm_ordhist d on 
  c.order_no = d.order_no and 
  c.order_ext = d.order_ext and 
  c.line_no = d.line_no

- Jeff
http://weblogs.sqlteam.com/JeffS

chrpeter
Starting Member

31 Posts

Posted - 2008-03-03 : 14:49:41

I actually did that to begin with, and after searching these forums and trying this format:
left outer join (select * from fm_ordhist where line_no <> 999) AS d on
c.order_no = d.order_no and
c.order_ext = d.order_ext and
c.line_no = d.line_no

The difference of specifying the d.line_no <> 999 in the join clause instead of the where clause made the difference.

Thanks for your reply though, because if you hadn't replied I wouldn't have tried them both together!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-03 : 14:57:12

You can also write the condition right into your join clause like this:


left outer join fm_ordhist d on
  c.order_no = d.order_no and
  c.order_ext = d.order_ext and
  c.line_no = d.line_no and
  line_no <> 999

- Jeff
http://weblogs.sqlteam.com/JeffS

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-03-03 : 19:17:30

The old style and new style outer joins are not equivalent when joining on more than one column. The new style is more exact so you may well have two different result sets.

=======================================
Society is like a stew. If you don't keep it stirred up you get a lot of scum on the top. -Edward Abbey, naturalist and author (1927-1989)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources