FULL OUTER JOIN Problem - 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 2000 Forums

Transact-SQL (2000)

FULL OUTER JOIN Problem

Author

Topic

EnlilCox
Starting Member

3 Posts

Posted - 2002-09-13 : 06:27:08

I am having a problem with a full outer join.. I am trying to join a table onto itself to get a list of descriptions that match and dont match between two specific IDs. It returns more rows than it should. I have checked the execution plan and it appears to be doing a a join with table a and a.id=101 against all of b and then b and b.id=212 against all of a and joining them together rather than filtering first.

Other than using derived tables (with the filtering in, which does solve) is there a better way ?

Thanks
Stuart

select a.desc, b.desc
from mytable a
full outer join
mytable b
on
a.desc = b.desc
and b.id=212
and a.id=101

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-13 : 06:45:08

not sure what you want.
maybe

select a.desc, b.desc
from mytable a
full outer join
mytable b
on
a.desc = b.desc
where (b.id=212 or b.id is null)
and (a.id=101 or a.id is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

EnlilCox
Starting Member

3 Posts

Posted - 2002-09-13 : 07:07:04

Ta but it didn't help

The table is something like
PKID ID Desc
1 1 Red
2 1 Blue
3 1 Green
4 2 Red
5 2 Blue
6 2 Yellow
7 3 Red
8 3 Blue

What I want is

Desc Desc
Red Red
Bluw Blue
Green NULL
NULL Yellow

So its a full outer join or IDs 1 & 2 but not 3

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-13 : 07:20:44

select a.desc, b.desc
from mytable a
full outer join mytable b
on a.desc = b.desc and a.id = 1 and b.id = 2
where (b.id = 2 or b.id is null)
and (a.id = 1 or a.id is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources