473,394 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

OUTER JOIN problem

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_languagecheckpervg
@varugruppkod varchar(20),
@sprakkod int

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

exec ('master..xp_cmdshell "bcp ##q_tbl_languagecheckpervg out
c:\outpath\adhoc\'+@varugruppkod+'.xls -Usa -P13hla -c -C"')

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

rgds
Matt
Jul 20 '05 #1
8 4949
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**@fruitsalad.org> wrote in message
news:b6**************************@posting.google.c om...
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_languagecheckpervg
@varugruppkod varchar(20),
@sprakkod int

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

exec ('master..xp_cmdshell "bcp ##q_tbl_languagecheckpervg out
c:\outpath\adhoc\'+@varugruppkod+'.xls -Usa -P13hla -c -C"')

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

rgds
Matt

Jul 20 '05 #2
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_languagecheckpervg
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)
Jul 20 '05 #3
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_languagecheckpervg
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
Jul 20 '05 #4
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!
Jul 20 '05 #5
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)
Jul 20 '05 #6
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
Jul 20 '05 #7
From the SQL 2000 Books Online:

<Excerpt href="acdata.chm::/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*******@yahoo.com> wrote in message
news:iGo6d.128183$MQ5.77602@attbi_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

Jul 20 '05 #8
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)
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
6
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON (...
3
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2...
1
by: Nikolaus Rumm | last post by:
Hello, at first: I am a total SQL idiot. My problem is as follows: CREATE TABLE ta_master ( masterid integer not null, title varchar(20), constraint idx_master primary key(masterid))
4
by: Anthony Robinson | last post by:
I was actually just wondering if someone could possibly take a look and tell me what I may be doing wrong in this query? I keep getting ambiguous column errors and have no idea why...? Thanks in...
3
by: deko | last post by:
From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite... SELECT qryTxToQ1.Q1Total, qryTxToQ2.Q2Total,...
3
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.