Given a table where a field (named ‘field3’) contians account numbers what query would fill another table with records where the only matched field contains all the account numbers from the original table and blank data for the other records in the new table?
I think what you want is an outer join.
Not sure I understand the question. Are you trying to copy the table but with all fields except the account number empty? Or are you trying to create a query which returns a result set as I described? Or are you trying to connect this account number table to some other existing table?
What dialect of SQL are you using? MS Access SQL, Oracle, MS real T-SQL, MySQL?
For the example’s sake, assume your source table has 4 fields, Field1 through Field4, and Field3 is your account number you want to preserve.
For the 2nd option, try something like
SELECT Field1=NULL, Field2=NULL, Field3, Field4=Null FROM TableName
GO
If you want zeros or empty strings in the result instead of NULLs the query is almost same:
SELECT Field1=0, Field2=’’, Field3, Field4=’’ FROM SourceTableName
GO
If you were trying to copy the data to another existing table and want NULLS for the other fields you can use a simple SELECT with an INSERT like this:
INSERT INTO TargetTable SELECT Field3 FROM SourceTable
GO
There are additonal options avaailable on the INSERT to control whther yuo get NULLs r DEFAULT values or … for the omitted fields.
I hope one ofthese willget you started in the right direction.