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

selectar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into##q_tbl_languagecheckpervg
fromar LEFT OUTER JOIN arb ON ar.artnr = arb.artnr
wherear.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
MattHi

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" <matt@.fruitsalad.org> wrote in message
news:b609190f.0409250018.309f7157@.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|||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.

selectar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into##q_tbl_languagecheckpervg
fromar
LEFT OUTER JOIN arb
ON ar.artnr = arb.artnr
AND arb.sprakkod = @.sprakkod
wherear.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)|||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.
>
> selectar.artnr,
> ar.artbeskr,
> arb.artbeskr AS 'sprak'
> into##q_tbl_languagecheckpervg
> fromar
> LEFT OUTER JOIN arb
> ON ar.artnr = arb.artnr
> AND arb.sprakkod = @.sprakkod
> wherear.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|||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!|||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)|||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|||From the SQL 2000 Books Online:

<Excerpt href="http://links.10026.com/?link=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" <leyandrew@.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|||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)

OUTER JOIN issue

Hi all,
I have two tables (for example, table1, table2) where table1 holds thesame data as table2 but also has other rows that are no contained intable2.
Now if I performed the following query...
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
... I expect to get the rows that are not contained in both tables. Butfor some reason I don't get this. I get all the rows from tabel1.
Is my thinking of what theLEFT OUTER JOIN query wrong, or is the query wrong?
To get around my problem, I had to do the following
SELECT table1.name
FROM table1
WHERE table1.table_name not IN (SELECT table_name FROM table2)
I would have preferred to have solved this with theLEFT OUTER JOIN though
Thanks
Tryst
I think you are a little confused about outer joins...
Left/Right Outer joins 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
Say I have
Table1
Id Name Age
-----
1 a 20
2 b 30
3 c 22
Table2
Id Sex
---
1 M
2 F
Say I want id,name,age,sex(if available) then I would use left join
Select id,name,age,sex from table1 left join table2 on table1.id = table2.id
This will return
Id name age sex
------
1 a 20 M
2 b 30 F
3 c 22 <null>
|||You need to add a WHERE condition to return the results you want from the LEFT OUTER JOIN:

SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
WHERE table2.table_name IS NULL

What would probably perform better for you is this:

SELECT table1.name
FROM table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.table_name = table2)

Check both execution plans/execution time in Query Analyzer to see which one performs better.

|||Ok, thanks guys.
Why is the inner SELECT query selecting NULL from table2? Whats it use?
Tryst
|||

Tryst wrote:

Why is the inner SELECT query selecting NULL from table2? Whats it use?

The list of column names is immaterial for the EXISTS. What isimportant is the WHERE condition. I have a habit of using NULL,partly because I read somewhere that it uses less resources than a * ora specific column (I am not sure whether or not that is true).

Orphaned transaction in sql 2005

Once in a while we end up with an orphaned transaction in sql 2005 around
the same application and it holds locks and the only way to get rid of it is
kill it.
So what causes it ? According to the app owner, they close the connection
and I have to say they do because the app is called so often and 99% of the
time it works fine and then once in 2 or 3 months, it leaves the orphaned
transaction.
The driver is a Java driver.. But I've seen this behavior on non Java
drivers as well.
So does SQL have any way of automatically killing orphaned transactions
Thank youRomeo,
The problem is that the SQL Server does not know it is an orphaned
transaction. If it knew, it would drop the transaction.
My anecdotal experience has been, if the machine that initiated the orphaned
transaction is logged off the domain, then logged back in the orphaned
transaction will ofter terminate. Apparently (real non-technical here) the
login back into the domain lets the SQL Server say to itself, "Hey, this
transaction must be orphaned because the machine I am doing it for just
logged in." But, I don't think I have ever seen this documented. (And it
is still a pain in the neck to track down the user and get them to log out,
then log back in.)
For orphaned DTC transactions, the SPID is -2, which you can find and kill.
This is documented in sp_who.
http://msdn2.microsoft.com/en-us/library/ms174313.aspx
RLF
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you|||> So does SQL have any way of automatically killing orphaned transactions
SQL Server will rollback an open transaction when the client disconnects so
I suspect that network connection is still open. Get the value of
client_tcp_port from sys,dm_exec_connections and check against a netstat
list on the client machine. If you see that the port is in use according to
netstat, then the connection is still open as far as the network layer and
SQL Server are concerned.
I don't know anything about your application other than you are using a java
driver with a SQL 2005 back end. Most drivers support some form of
connection pooling which means that a connection isn't actually closed when
the application closes the connection; the connection is simply returned to
the pool and is available for reuse. If a transaction is open when a pooled
connection is closed, it could remain open until the connection is either
reused or removed from the pool. The exact behavior depends on a number of
factors, such as the specific driver and how the transaction was initiated
(server vs. client API).
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
It may be than all is fine as long as the expected code path is executed.
Make sure that the application cleans up (e;g; WHILE @.@.TRANCOUNT > 0
ROLLBACK and close connection) following exceptions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you