Linking issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Linking issue

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-03-07 : 15:58:12

I'm working with 3 tables. oehdrhst, oelinhst, oehdraud.

Table layouts are:

oehdrhst


ord_typ   ord_no    inv_no
  O        1234        5555
  O        1234        6666
  Q        5678        2348

oelinhst


ord_type  ord_no  item  line_no    inv_no
O          1234    ABC      1        5555
O          1234    DEF      2        5555
Q          1555    XXX      1        6666
O          5678    YYY      1        2348
O          5678    TTT      2        2348

oehdraud


ord_type   ord_no   Action
O           1234       A
O           5678       A

Here is what I'd like to see:


ord_type   ord_no  item   line_no   Action
O           1234   ABC       1        A
O           1234   DEF       2        A
Q           1555   XXX       1       NULL
O           5678   YYY       1        A
O           5678   TTT       2        A

I've tried this and it doesn't seem to work.


select *
from oelinhst join
oehdrhst on oelinhst.ord_no = oehdrhst.ord_no and oelinhst.inv_no =
oehdrhst.inv_no join oehdraud on oehdraud.ord_type = oehdrhst.ord_type and oehdraud.ord_no = oehdrhst.ord_no

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-10 : 10:35:50

Try

select *
from oelinhst join
oehdrhst on oelinhst.ord_no = oehdrhst.ord_no and oelinhst.inv_no =
oehdrhst.inv_no left join oehdraud on oehdraud.ord_type = oehdrhst.ord_type and oehdraud.ord_no = oehdrhst.ord_no

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources