[dba-SQLServer] join pulls no records

Doris Manning mikedorism at verizon.net
Fri Dec 1 14:46:58 CST 2006


John,

By any chance, are you using the asterisk (*) to return all the fields in
the matching record?  If so, I've had a lot of problems with that and have
learned to always select the fields I need.

Sounds like to you need to look up UPDATE FROM in the BOL.

Doris Manning

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, December 01, 2006 3:40 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] join pulls no records

What it turned out to be was the join type.  I was using an inner join,
which in Access will return whatever fields you pull out of whatever side
you wish.  In this case, I had to open the join properties and specify to
return records from one or both sides, otherwise it just wouldn't return any
records.  

Even then the results are strange.  I want to be able to edit a field pulled
from one of the tables.  Essentially set a field true in one table if there
is a matching record in the other table (thus the inner join).  I can edit
the field one time (saving the change), however if I try to immediately
change the field to some other value, I get an error "Cannot edit rows with
unknown keys".  If I requery (pull the records again) I can now edit it one
more time then the same error.

So much to know, so little time.  I do have an identically named field in
both tables - the PK in both tables is called MCTie2.  Perhaps it is getting
confused?  Perhaps I am getting confused?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Friday, December 01, 2006 3:23 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] join pulls no records

Hi John:

Have you checked to see if some of the fields, in the index, that you are
depending have 'nulls'. In some cases a single null will negate the entire
select. Oracle has a NVL() function that resolve this but I am sure MS SQL
does also. Try reversing the joins (outer - inner) and see if there are any
results.

HTH
Jim    

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, December 01, 2006 12:00 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] join pulls no records

I have two tables.  Each has an nvarchar 50 field which contains a string
consisting of the first 5 characters of about 5 different fields, which is
called in the client's industry a "match code" and is used to find the same
name / address record in a different table.  I have made the field the PK,
so there is an index and it is unique.  A join of the two tables pulls no
records.  I can manually look in the two tables and see visually identical
data, i.e. it looks like the match code matches but an inner join does not
pull records.
 
Any clue why not?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com






More information about the dba-SQLServer mailing list