Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 5th, 2006, 12:48 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default JOIN operation

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?

 
Old June 5th, 2006, 01:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by U.N.C.L.E.
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.
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
 
Old June 5th, 2006, 01:13 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old June 5th, 2006, 01:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by U.N.C.L.E.
 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.
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
 
Old June 5th, 2006, 01:52 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 5th, 2006, 02:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 5th, 2006, 02:11 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

After further digging...

table2 had multiple entries for some of the MFG codes.

Thanks for the help.

 
Old July 10th, 2006, 06:52 AM
Registered User
 
Join Date: Jun 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




 
Old July 10th, 2006, 06:52 AM
Registered User
 
Join Date: Jun 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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%'











Similar Threads
Thread Thread Starter Forum Replies Last Post
Working of OR operation? bvpsekhar MySQL 0 May 16th, 2007 04:14 AM
calculation operation vinny07 Excel VBA 0 March 1st, 2007 06:32 AM
OPeration must be updated khzahid Classic ASP Basics 1 April 27th, 2005 06:01 PM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
No log operation acko SQL Server 2000 0 April 2nd, 2004 04:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.