Joins - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Joins

Author

Topic

venkatkrishna
Starting Member

12 Posts

Posted - 2007-06-15 : 02:25:19

Hello,

Can we do outer join on the static text like.
select t.area_cd, t.area_desc, f2.last_name mgr_last, t.territory_short_desc,
t.team_cd, t.team_desc
from a t,
b f,
b f2
where t.setid = 'USPFE'
and t.active_fg = 'Y'
and f.territory_id(+) = t.territory_id
and f.setid(+) = t.setid
and f.field_active(+) = 'A'
and f2.territory_id(+) = t.rpt_lvl1_terr
and f2.setid(+) = t.setid
and f2.field_active(+) = 'A'
and f2.full_name(+) is not null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-15 : 02:29:17

Yes. MS SQL has OUTER JOIN. Please refer to BOOKS ONLINE on OUTER JOIN syntax

Kristen
Test

22859 Posts

Posted - 2007-06-15 : 04:12:31

Does MS SQL still support the, deprecated, style of:

...
and f.setid *= t.setid
and f.field_active *= 'A'
...

TBH I thought that was the correct syntax for Oracle

Kristen

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-15 : 05:45:50

Try:

SELECT t.area_cd, t.area_desc, f2.last_name mgr_last, t.territory_short_desc,
t.team_cd, t.team_desc
FROM a t
    RIGHT JOIN b f
        ON t.setid = 'USPFE'
            AND t.active_fg = 'Y'
            AND f.territory_id = t.territory_id
            AND f.setid = t.setid
            AND f.field_active = 'A'
    RIGHT JOIN b f2
        ON f2.territory_id = t.rpt_lvl1_terr
            AND f2.setid = t.setid
            AND f2.field_active = 'A'
            AND f2.full_name IS NOT NULL

Kristen
Test

22859 Posts

Posted - 2007-06-18 : 04:22:27

"RIGHT JOIN"

Yuck! I hate RIGHT OUTER JOINS. Hard for humans to read, and therefore more inclined to lead to mistakes during coding / maintenance.

IMHO better to rewrite the code to ~~sue~~ use LEFT Outer Joins.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-18 : 04:24:15

"to sue LEFT Outer Joins."

To SUE? Why?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-06-18 : 05:23:42

'Coz I can't spell USE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-18 : 06:16:45

quote:
Originally posted by Kristen

'Coz I can't spell USE

USE Dictionary

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-18 : 06:23:05

quote:
Originally posted by Kristen

"RIGHT JOIN"

Yuck! I hate RIGHT OUTER JOINS. Hard for humans to read, and therefore more inclined to lead to mistakes during coding / maintenance.

IMHO better to rewrite the code to ~~sue~~ use LEFT Outer Joins.

Kristen

I generally prefer LEFT JOINs but given the original code the RIGHT JOIN seemed easier. The only alternative I can think of here is a nested LEFT JOIN which I am not sure is any easier to read.

FROM b f2
    LEFT JOIN (
            b f
            LEFT JOIN a t
                ON t.setid = 'USPFE'
                AND t.active_fg = 'Y'
                AND f.territory_id = t.territory_id
                AND f.setid = t.setid
                AND f.field_active = 'A'
        )
        ON f2.territory_id = t.rpt_lvl1_terr
            AND f2.setid = t.setid
            AND f2.field_active = 'A'
            AND f2.full_name IS NOT NULL

Kristen
Test

22859 Posts

Posted - 2007-06-18 : 10:18:31

"USE Dictionary"

Could not locate entry in sysdatabases for database 'Dictionary'. No entry found with that name. Make sure that the name is entered correctly.

"The only alternative I can think of here"

I think the "RIGHT JOIN b f" is only there as an EXISTS test as the SELECT doesn't include any columns from it. That might be an option for a LEFT JOIN alternative?

Kristen

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-18 : 10:57:33

quote:
Originally posted by Kristen
I think the "RIGHT JOIN b f" is only there as an EXISTS test as the SELECT doesn't include any columns from it. That might be an option for a LEFT JOIN alternative?

You may be right, although I suspect you could lose some rows in the result set if there are multiple rows for a join to F on a given condition. On looking at the SELECT columns this does not look as though it matters.

Kristen
Test

22859 Posts

Posted - 2007-06-18 : 13:35:19

"you could lose some rows in the result set if there are multiple rows for a join to F on a given condition"

Good point - but as you say: with the current Select they will just be Dupes!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources