right 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)

right outer join problem

Author

Topic

cez
Starting Member

37 Posts

Posted - 2002-09-30 : 04:24:06

Hi

I have 2 tables, one for languges and one for articles.
the 'language' table has: lng_id char(2), lng_isarticle bit
for example:
en, 1
fr, 1
ge, 1
ro, 0

the articles table has :id int, br_filename varchar(255), lng_id
for example:
1, 'file1.pdf', 'en'
2, 'file2.pdf', 'fr'

How I need a query that returns (all the languages that are for articles and the articles' name - if there is no article, i need to return blank.

'file1.pdf', 'en'
'file2.pdf', fr
'', ge

This is what I wrote:
SELECT isnull(b.br_filename, '') as br_filename, a.lng_id
FROM articles b RIGHT OUTER JOIN languages a
ON a.lng_isarticle = 1 AND b.br_id = @br_id AND a.lng_id = b.lng_id

But unfortunately this query returns all the languages, not the one that are setted for articles ( bit for lng_isarticle = 1)

PLEASE HELP ME.
Thanks

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-30 : 04:47:57

Move the a.lng_isarticle = 1 condition into a WHERE clause. The semantics for conditions only involving one table's columns in an outer join are sometimes not what one expects!
See also http://www.sqlteam.com/item.asp?ItemID=11122

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-30 : 04:49:53

SELECT isnull(b.br_filename, '') as br_filename, a.lng_id
FROM articles b
RIGHT OUTER JOIN
languages a
ON b.br_id = @br_id AND a.lng_id = b.lng_id
where a.lng_isarticle = 1

I prefer left joins

SELECT isnull(b.br_filename, '') as br_filename, a.lng_id
FROM languages a
LEFT OUTER JOIN
articles b
ON b.br_id = @br_id AND a.lng_id = b.lng_id
where a.lng_isarticle = 1

==========================================
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.

cez
Starting Member

37 Posts

Posted - 2002-09-30 : 04:51:00

quote:

Move the a.lng_isarticle = 1 condition into a WHERE clause. The semantics for conditions only involving one table's columns in an outer join are sometimes not what one expects!
See also http://www.sqlteam.com/item.asp?ItemID=11122

thanks - nice article

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources