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