I have 2 tables. Say table1 has 100 rows.

Table1 has a field 'MFG' which is a 2 char code.

I want to replace the 2 char code with a lookup value from table2 which may or may not include all 2 char codes...

So I do:

SELECT TABLE2.MFG_CODE FROM table1 LEFT OUTER JOIN table2 ON table1.MFG=table2.MFG;

Because table2 does not contain all possible MFG codes in table1, the query is returning more than the number of rows originally in table1.

Question: Is there a simpler way to do a lookup of the value I need from table2 while returning all (but not more) rows from table1?

Are you sure about that?

An OUTER JOIN will preserve the number of rows in the "left" table (in your case, table1) and include values from the "right" table (table2) if the JOIN condition matches. If there are no corresponding rows in the right hand table, the values in the resultset from that table will be set to NULL.

As your query is written, your resultset will exactly contain all the rows from table1, neither more nor less. But, since you are selecting values from table2, some of those values will be NULL - the ones where there is no matching value from table1.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Yes, in my situation table1 contains 6503 rows, the query is returning 22706 rows...

I should add, table2 can contain MFG codes that are not in table1.


Update:
I just tried the following SQL statement:

SELECT (SELECT TOP 1 MFG_CODE FROM table2 WHERE table2.MFG=table1.MFG) AS MFG_CODE FROM table1;

which returned the exact number of rows in table1, but isn't this method very inefficient?

With all due respect, something doesn't ring true.

A LEFT OUTER JOIN will "preserve" the rows on "left" side (your table1) and include only those rows from the right which match the JOIN condition. Any rows from the left side which have no corresponding entry on the right will have the right side column values set to NULL.

Thus, an OUTER JOIN will only have as many rows as are in the preserved table.

What SQL implementation are you using, and is that earlier query you posted really the query you were running, or was it 'editorialized' for posting here?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I am using SQL Server Management Studio (2005) connecting to SQL Server 2000.

The code has been editorialized to change only the table names.

table1 contains multiple entries with the MFG code and other fields.
table2 contains a list of the MFG codes and a MFG_CODE description.

Not all possible MFG codes are contained in table2, only the ones I'm interested in (the rest as you mentioned should return NULL).

The updated SQL statement returns the correct result set with NULL in the MFG field which do not have a match.

If table1 has 6503 rows, then an OUTER JOIN with this table as the preserved table will only have 6503 rows in it (possibly less depending on WHERE clauses, but certainly not more).

I see nothing wrong with your original query. It should only have 6503 rows in its resultset.

I asked the question about editorializing because on too many occasions to mention, someone removes a key part of a query they are having problems with "in the interests of simplicity", and materially changes the result.

Just wanted to make sure that wasn't happening here...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

After further digging...

table2 had multiple entries for some of the MFG codes.

Thanks for the help.

I want to replace the 2 char code with a lookup value from table2 which may or may not include all 2 char codes...

Explain this in more simple language. do you want to replace all the data in MFG column in tbale1 with MFG column in table2 ??

check if any of these can solve your problem.

SELECT table2.MFG_CODE
FROM table1
inner join table2 ON table1.mfg= table2.mfg
where table1.MFG_CODE like '%TWO CHARAC CODE%'

OR

I want to replace the 2 char code with a lookup value from table2 which may or may not include all 2 char codes...

Explain this in more simple language. do you want to replace all the data in MFG column in tbale1 with MFG column in table2 ??

check if any of these can solve your problem.

SELECT table2.MFG_CODE
FROM table1
inner join table2 ON table1.mfg= table2.mfg
where table1.MFG_CODE like '%TWO CHARAC CODE%'