OUTER JOIN problem

[Matt]

Hello

I have to tables ar and arb, ar holds articles and a swedish
description, arb holds descriptions in other languages.

I want to retreive all articles that match a criteria from ar and also
display their corresponding entries in arb, but if there is NO entry
in arb I still want it to show up as NULL or something, so that I can
get the attention that there IS no language associated with that
article.

I tried to use the following but it does not work correctly

create procedure q_spr_languagec heckpervg
@varugruppkod varchar(20),
@sprakkod int

AS
select ar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into ##q_tbl_languag echeckpervg
from ar LEFT OUTER JOIN arb ON ar.artnr = arb.artnr
where ar.varugruppkod = @varugruppkod and
arb.sprakkod = @sprakkod

exec ('master..xp_cm dshell "bcp ##q_tbl_languag echeckpervg out
c:\outpath\adho c\'+@varugruppk od+'.xls -Usa -P13hla -c -C"')

drop table ##q_tbl_languag echeckpervg
The problem being that if an article has NO entry in arb it will not
be shown at all.

rgds
Matt

[John Bell]

Hi

The left outer joing should show the items in ar even though there is no
entry in arb. Could you please post ddl and example data (see
http://www.aspfaq.com/etiquette.asp?id=5006 ) as well as SQL Server
version.

John

"Matt" <ma**@fruitsala d.org> wrote in message
news:b6******** *************** ***@posting.goo gle.com...

Hello

I have to tables ar and arb, ar holds articles and a swedish
description, arb holds descriptions in other languages.

I want to retreive all articles that match a criteria from ar and also
display their corresponding entries in arb, but if there is NO entry
in arb I still want it to show up as NULL or something, so that I can
get the attention that there IS no language associated with that
article.

I tried to use the following but it does not work correctly

create procedure q_spr_languagec heckpervg
@varugruppkod varchar(20),
@sprakkod int

AS
select ar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into ##q_tbl_languag echeckpervg
from ar LEFT OUTER JOIN arb ON ar.artnr = arb.artnr
where ar.varugruppkod = @varugruppkod and
arb.sprakkod = @sprakkod

exec ('master..xp_cm dshell "bcp ##q_tbl_languag echeckpervg out
c:\outpath\adho c\'+@varugruppk od+'.xls -Usa -P13hla -c -C"')

drop table ##q_tbl_languag echeckpervg
The problem being that if an article has NO entry in arb it will not
be shown at all.

rgds
Matt

[Hugo Kornelis]

On 25 Sep 2004 01:18:52 -0700, Matt wrote:

I want to retreive all articles that match a criteria from ar and also
display their corresponding entries in arb, but if there is NO entry
in arb I still want it to show up as NULL or something, so that I can
get the attention that there IS no language associated with that
article.

select ar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into ##q_tbl_languag echeckpervg
from ar
LEFT OUTER JOIN arb
ON ar.artnr = arb.artnr
AND arb.sprakkod = @sprakkod
where ar.varugruppkod = @varugruppkod

You should move the test for arb.sprakkok from the where clause to the
join criteria. Otherwise, the non-matching rows (having NULL in all arb
columns) will be removed because NULL (arb.sprakkod) will never be equal
to any @sprakkod.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Andrey]

Hugo Kornelis wrote:

On 25 Sep 2004 01:18:52 -0700, Matt wrote:

I want to retreive all articles that match a criteria from ar and also
display their corresponding entries in arb, but if there is NO entry
in arb I still want it to show up as NULL or something, so that I can
get the attention that there IS no language associated with that
article.

select ar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into ##q_tbl_languag echeckpervg
from ar
LEFT OUTER JOIN arb
ON ar.artnr = arb.artnr
AND arb.sprakkod = @sprakkod
where ar.varugruppkod = @varugruppkod

You should move the test for arb.sprakkok from the where clause to the
join criteria. Otherwise, the non-matching rows (having NULL in all arb
columns) will be removed because NULL (arb.sprakkod) will never be equal
to any @sprakkod.

Best, Hugo

knock-knock!

What is OUTER for here? LEFT JOIN will return all ar.* entries and linked arb.* where exist; for the
rest in arb.* columns you get NULL. So it works just fine for the example.

And i'm not sure you can use OUTER & LEFT together - never headr of that and found just an oracle
example, nothing in t-sql.

Wrong?

Thank you,
Andrey

[John Bell]

Hi

If you check books online ("From clause") you will see that OUTER is an
optional keyword in LEFT JOIN. Hugo spotted the reason why this did not
work.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On Tue, 28 Sep 2004 02:33:13 GMT, Andrey wrote:

knock-knock!

What is OUTER for here? (snip)And i'm not sure you can use OUTER & LEFT together - never headr of that and found just an oracle
example, nothing in t-sql.

Hi Andrey,

The syntax for a left outer join is:
<table-source> LEFT [OUTER] JOIN <table-source> ON <condition>

In other words: "OUTER" is an optional keyword (this is true for right
outer joins and full outer joins as well). Though I usually don't include
the OUTER keyword in my own code, I often do include it in newsgroups
postings, as it gives some extra documentation about what I'm doing.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Andrey]

Hugo Kornelis wrote:

On Tue, 28 Sep 2004 02:33:13 GMT, Andrey wrote:

knock-knock!

What is OUTER for here?

(snip)

And i'm not sure you can use OUTER & LEFT together - never headr of that and found just an oracle
example, nothing in t-sql.

Hi Andrey,

The syntax for a left outer join is:
<table-source> LEFT [OUTER] JOIN <table-source> ON <condition>

In other words: "OUTER" is an optional keyword (this is true for right
outer joins and full outer joins as well). Though I usually don't include
the OUTER keyword in my own code, I often do include it in newsgroups
postings, as it gives some extra documentation about what I'm doing.

Best, Hugo

Brrr-rrr... Checked books online and it says that yes, OUTER is optional. But does it mean that it
changes anything? As i understood - not really. Correct?

Also i've seen there an example where just JOIN is used(w/o INNER/LEFT/RIGHT/FULL).
But didn't really find any explanation of what's the difference? So can you please explain what just
JOIN does? Seems to me it should be same as INNER JOIN... I'm confused...
Thank you,
Andrey

[Dan Guzman]

From the SQL 2000 Books Online:

<Excerpt href="acdata.ch m::/ac_8_qd_09_0zqr .htm">

Inner joins return rows only when there is at least one row from both tables
that matches the join condition. Inner joins eliminate the rows that do not
match with a row from the other table. Outer joins, however, return all rows
from at least one of the tables or views mentioned in the FROM clause, as
long as those rows meet any WHERE or HAVING search conditions. All rows are
retrieved from the left table referenced with a left outer join, and all
rows from the right table referenced in a right outer join. All rows from
both tables are returned in a full outer join

Microsoft® SQL ServerT 2000 uses these SQL-92 keywords for outer joins
specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN

RIGHT OUTER JOIN or RIGHT JOIN

FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax
for specifying outer joins based on using the *= and =* operators in the
WHERE clause. The SQL-92 syntax is recommended because it is not subject to
the ambiguity that sometimes results from the legacy Transact-SQL outer
joins.

</Excerpt>

Here's some examples:

SELECT *
FROM Table1
JOIN Table2 ON Col1 = Col2

Col1 Col2
----------- -----------
3 3

SELECT *
FROM Table1
LEFT JOIN Table2 ON Col1 = Col2

Col1 Col2
----------- -----------
1 NULL
3 3
5 NULL

SELECT *
FROM Table1
RIGHT JOIN Table2 ON Col1 = Col2

Col1 Col2
----------- -----------
NULL 2
3 3
NULL 4

SELECT *
FROM Table1
FULL JOIN Table2 ON Col1 = Col2

Col1 Col2
----------- -----------
NULL 2
3 3
NULL 4
5 NULL
1 NULL

SELECT *
FROM Table1
CROSS JOIN Table2

Col1 Col2
----------- -----------
1 2
3 2
5 2
1 3
3 3
5 3
1 4
3 4
5 4

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Andrey" <le*******@yaho o.com> wrote in message
news:iGo6d.1281 83$MQ5.77602@at tbi_s52...

Hugo Kornelis wrote:

On Tue, 28 Sep 2004 02:33:13 GMT, Andrey wrote:

knock-knock!

What is OUTER for here?

(snip)

And i'm not sure you can use OUTER & LEFT together - never headr of that
and found just an oracle example, nothing in t-sql.

Hi Andrey,

The syntax for a left outer join is:
<table-source> LEFT [OUTER] JOIN <table-source> ON <condition>

In other words: "OUTER" is an optional keyword (this is true for right
outer joins and full outer joins as well). Though I usually don't include
the OUTER keyword in my own code, I often do include it in newsgroups
postings, as it gives some extra documentation about what I'm doing.

Best, Hugo

Brrr-rrr... Checked books online and it says that yes, OUTER is optional.
But does it mean that it changes anything? As i understood - not really.
Correct?

Also i've seen there an example where just JOIN is used(w/o
INNER/LEFT/RIGHT/FULL).
But didn't really find any explanation of what's the difference? So can
you please explain what just JOIN does? Seems to me it should be same as
INNER JOIN... I'm confused...
Thank you,
Andrey

[Hugo Kornelis]

On Wed, 29 Sep 2004 01:59:42 GMT, Andrey wrote:

Brrr-rrr... Checked books online and it says that yes, OUTER is optional. But does it mean that it
changes anything? As i understood - not really. Correct?
Hi Andrey,

The keyword OUTER is optional. It doesn't matter if you specify it or not,
so the only difference between "LEFT JOIN" and "LEFT OUTER JOIN" is five
letters and a blank.

Also i've seen there an example where just JOIN is used(w/o INNER/LEFT/RIGHT/FULL).
Like OUTER, INNER is an optional keyword as well. If you see just JOIN,
it's an INNER JOIN. I never leave that keyword out, as I find it rather
confusing. The full list of join types, with optional keywords between
[brackets], is:

* [INNER] JOIN
* LEFT [OUTER] JOIN
* RIGHT [OUTER] JOIN
* FULL [OUTER] JOIN
* CROSS JOIN

But didn't really find any explanation of what's the difference? So can you please explain what just
JOIN does? Seems to me it should be same as INNER JOIN... I'm confused...

I think Dan's reply covered this already.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)