Query acting strangely

I have some problems with this query. It returns some information but not all.
SELECT PAGE0.DOL, PAGE3.STATUS1, PAGE0.STATUS
FROM PAGE0 INNER JOIN PAGE3. ON PAGE0.SERIAL = PAGE3.P_SERIAL
WHERE (PAGE3.STATUS1 <> 'TOA') AND (PAGE0.STATUS = 'OPEN')
For whatever reason this query doesn't return all the info in the table. I tested it somewhat and I noticed that if I reference only one table in the WHERE clouse all is working fine (except I can not filter my query the way I want to). If I remove PAGE3.
STATUS <> 'TOA' then I get all the results. Is there sometihing wrong with this query? I am using SQL server 2000 SP3.
Thanks For any help.
Sebastian
On Thu, 8 Apr 2004 03:46:03 -0700, Sebastian wrote:

>I have some problems with this query. It returns some information but not all.
>SELECT PAGE0.DOL, PAGE3.STATUS1, PAGE0.STATUS
>FROM PAGE0 INNER JOIN PAGE3. ON PAGE0.SERIAL = PAGE3.P_SERIAL
>WHERE (PAGE3.STATUS1 <> 'TOA') AND (PAGE0.STATUS = 'OPEN')
>For whatever reason this query doesn't return all the info in the table. I tested it somewhat and I noticed that if I reference only one table in the WHERE clouse all is working fine (except I can not filter my query the way I want to). If I remove PAGE3
.STATUS <> 'TOA' then I get all the results. Is there sometihing wrong with this query? I am using SQL server 2000 SP3.
>Thanks For any help.
>Sebastian
I can't answer this without more information. Please post a repro
script that can help me reproduce and diagnose the problem.
A repro script consists of:
* DDL (create table statements) for the relevant tables, constraints
and indexes,
* insert statements with enough sample data to reproduce the error,
* the text of the query you're having trouble with.
Run the repro script in an empty (test) database to check that it
really reproduces the observed behaviour. Then, post it here, along
with the output you expected from that query.
Many of the regulars here will step in to help you correct the problem
if you post as suggested. Without that information, though, it's very
hard to help us at all (it's like phoning the garage, saying "why
doesn't my car work - it worked fine yesterday")
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||i am going to try to do what you just told me. Thank You. I will post my results soon.
Thank You again

Querstion: update on joined field

Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.nameYes you can update the table. The inner join serves to limit the rows to be
updated. Much easier than writing a WHERE clause for the t1.name clause.
Nathan H. Omukwenyi
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:17C998CC-B9C4-42D0-9650-C7331D3A7613@.microsoft.com...
> Is it possible to update the filed used in the inner join
> Update t1 set t1.name=t2.name2
> From t1 inner join t2 on t1.name = t2.name
>|||On Tue, 6 Jun 2006 19:23:01 -0700, JIM.H. wrote:

>Is it possible to update the filed used in the inner join
>Update t1 set t1.name=t2.name2
>From t1 inner join t2 on t1.name = t2.name
>
Hi Jim,
Yes, this is possible, BUT:
1. The UPDATE ... FROM syntax is not standard and therefor not portable.
The syntax can allso become quite confusing.
2. If a row in the table to be updated is matched by more than one row
in the source table(s), the row will actually be updated several times
and only the "last" result sticks. Since the order of evaluation is
never guaranteed, the result will be undefined. Running such a query
won't cause an error or even a warning message!
Hugo Kornelis, SQL Server MVP

Querstion: update on joined field

Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.nameYes you can update the table. The inner join serves to limit the rows to be
updated. Much easier than writing a WHERE clause for the t1.name clause.
Nathan H. Omukwenyi
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:17C998CC-B9C4-42D0-9650-C7331D3A7613@.microsoft.com...
> Is it possible to update the filed used in the inner join
> Update t1 set t1.name=t2.name2
> From t1 inner join t2 on t1.name = t2.name
>|||On Tue, 6 Jun 2006 19:23:01 -0700, JIM.H. wrote:
>Is it possible to update the filed used in the inner join
>Update t1 set t1.name=t2.name2
>From t1 inner join t2 on t1.name = t2.name
>
Hi Jim,
Yes, this is possible, BUT:
1. The UPDATE ... FROM syntax is not standard and therefor not portable.
The syntax can allso become quite confusing.
2. If a row in the table to be updated is matched by more than one row
in the source table(s), the row will actually be updated several times
and only the "last" result sticks. Since the order of evaluation is
never guaranteed, the result will be undefined. Running such a query
won't cause an error or even a warning message!
--
Hugo Kornelis, SQL Server MVP

q; the owner of the table

Hello,
I need to user full name for the table as seen below.
SELECT @.RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all
users if the creator is not dbo?JIM
You will have to identify an user and then creating dynamic sql if I
understood you properly.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> Hello,
> I need to user full name for the table as seen below.
> SELECT @.RowCount = COUNT(*)
> FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> T2 may be re-created by different users, how can I get this working for
> all
> users if the creator is not dbo?
>|||Thanks for the reply, I am trying to select the data whoever creates it, if
the owner is not dbo, my query is not working, owner may be many users since
people drop it an recreate it from time to time.
"Uri Dimant" wrote:

> JIM
> You will have to identify an user and then creating dynamic sql if I
> understood you properly.
>
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>
>|||JIM
So specify
CREATE TABLE user.TableName (col INT)
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>|||JIM.H. wrote:
> Thanks for the reply, I am trying to select the data whoever creates it, i
f
> the owner is not dbo, my query is not working, owner may be many users sin
ce
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
Having a table that is repeatedly dropped/created by your users seems
like a bad idea. Why not create a permanent table, owned by dbo, and
have your users TRUNCATE it instead of dropping/creating it? Or better
yet, explain why the need to drop/create exists, and perhaps we can
offer a better idea?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>|||Thanks for the reply. I agree however that is the part of the code I do not
have control over, I just need to figure out a way select it based on the
different owner.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Having a table that is repeatedly dropped/created by your users seems
> like a bad idea. Why not create a permanent table, owned by dbo, and
> have your users TRUNCATE it instead of dropping/creating it? Or better
> yet, explain why the need to drop/create exists, and perhaps we can
> offer a better idea?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

q; the owner of the table

Hello,
I need to user full name for the table as seen below.
SELECT @.RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all
users if the creator is not dbo?JIM
You will have to identify an user and then creating dynamic sql if I
understood you properly.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> Hello,
> I need to user full name for the table as seen below.
> SELECT @.RowCount = COUNT(*)
> FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> T2 may be re-created by different users, how can I get this working for
> all
> users if the creator is not dbo?
>|||Thanks for the reply, I am trying to select the data whoever creates it, if
the owner is not dbo, my query is not working, owner may be many users since
people drop it an recreate it from time to time.
"Uri Dimant" wrote:
> JIM
> You will have to identify an user and then creating dynamic sql if I
> understood you properly.
>
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> > Hello,
> > I need to user full name for the table as seen below.
> >
> > SELECT @.RowCount = COUNT(*)
> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> >
> > T2 may be re-created by different users, how can I get this working for
> > all
> > users if the creator is not dbo?
> >
>
>|||JIM
So specify
CREATE TABLE user.TableName (col INT)
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>> JIM
>> You will have to identify an user and then creating dynamic sql if I
>> understood you properly.
>>
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>> > Hello,
>> > I need to user full name for the table as seen below.
>> >
>> > SELECT @.RowCount = COUNT(*)
>> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
>> >
>> > T2 may be re-created by different users, how can I get this working for
>> > all
>> > users if the creator is not dbo?
>> >
>>|||JIM.H. wrote:
> Thanks for the reply, I am trying to select the data whoever creates it, if
> the owner is not dbo, my query is not working, owner may be many users since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
Having a table that is repeatedly dropped/created by your users seems
like a bad idea. Why not create a permanent table, owned by dbo, and
have your users TRUNCATE it instead of dropping/creating it? Or better
yet, explain why the need to drop/create exists, and perhaps we can
offer a better idea?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>> JIM
>> You will have to identify an user and then creating dynamic sql if I
>> understood you properly.
>>
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>> > Hello,
>> > I need to user full name for the table as seen below.
>> >
>> > SELECT @.RowCount = COUNT(*)
>> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
>> >
>> > T2 may be re-created by different users, how can I get this working for
>> > all
>> > users if the creator is not dbo?
>> >
>>|||Thanks for the reply. I agree however that is the part of the code I do not
have control over, I just need to figure out a way select it based on the
different owner.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > Thanks for the reply, I am trying to select the data whoever creates it, if
> > the owner is not dbo, my query is not working, owner may be many users since
> > people drop it an recreate it from time to time.
> >
> > "Uri Dimant" wrote:
> Having a table that is repeatedly dropped/created by your users seems
> like a bad idea. Why not create a permanent table, owned by dbo, and
> have your users TRUNCATE it instead of dropping/creating it? Or better
> yet, explain why the need to drop/create exists, and perhaps we can
> offer a better idea?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,Ok. I removed [, works fine.
"JIM.H." wrote:
> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...
> Ok. I removed [, works fine.
> "JIM.H." wrote:
>> Hello,
>> SELECT dbo.tSp.pID, dbo.tLo.oS
>> FROM dbo.tSp INNER JOIN
>> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
>> WHERE (dbo.tLo.oS = N'[MyText]')
>> This works without Where and I see MyText available in oS column. Why
>> does
>> it not bring anything when Where is there?
>> Thanks,|||SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp
INNER JOIN
dbo.tLo
ON dbo.tSp.SpID = dbo.tLo.SpID
AND dbo.tLo.oS = N'MyText'
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:C1220918-D358-4387-9268-94679031B403@.microsoft.com...
> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,Ok. I removed [, works fine.
"JIM.H." wrote:

> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...[vbcol=seagreen]
> Ok. I removed [, works fine.
> "JIM.H." wrote:
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,
Ok. I removed [, works fine.
"JIM.H." wrote:

> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>
|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...[vbcol=seagreen]
> Ok. I removed [, works fine.
> "JIM.H." wrote: