help, joining 2 tables based on many fields

[Ruairi]

Here is my problem. I am exporting data from a legacy flat file system to text files for DTS import to a SQL 2000 server. There is a production table and a price(inventory) table. In the legacy system the price is chosen at run time based on many parameters (group, grade, species, surface, etc..) so there is no natural PK -> FK relationship. I finally got the import of all this data working perfectly but now i need to insert records into a link table to relate the production and price(inventory) tables. All (or at least the vast majority) of the production records should have a corresponding price record. When i run this join querey i get 19,000 records. It should be more like 150,000. Can anyone see anything wrong with the way i'm doing this? Did i provide all the information you would need to help, or did i leave something out? Any help greatly appreciated. Below is the koin querey.

select 'prodrec'=P.rec, 'invrec'=I.rec from 
Production as P 
Full outer join inventory as I ON (P.grouprec = I.grouprec AND P.graderec = 
I.graderec AND P.speciesrec = I.speciesrec AND P.surfacerec = I.surfacerec AND
P.moisturerec = I.moisturerec AND P.endtrimrec = I.endtrimrec AND P.xthick = 
I.thickness AND P.xwidth = I.width AND P.length = I.length) 
where P.arearec = 1 and I.arearec = 1

P.S. arearec signifies what mill this production is coming from.
TIA Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Cagliostro]

You'll get more information if you put 'or' instead of 'and':
where P.arearec = 1 or I.arearec = 1 John Fill
ivfmd@mail.md

 

[Ruairi]

thanks, but production from one area should only be matched with prices from that area.
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Cagliostro]

I do not know what your problem is and what do you want to accomplish.
1. Why don't you put P.arearec into the join statement?
2. How did you get what where shoult be much many records than you really get?
John Fill
ivfmd@mail.md

 

[Cagliostro]

I do not know what your problem is and what do you want to accomplish.
1. Why don't you put P.arearec into the join statement?
2. How did you get what where should be much many records than you really get?
John Fill
ivfmd@mail.md

 

[tlbroadbent]

You have asked for a FULL OUTER JOIN which is defined as follows in BOL.

<i>&quot;A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.&quot;</i>

However, your WHERE clause restricts the returned records to those records where P.arearec = 1 and I.arearec = 1. Therefore, all records that don't match the criteria will be eliminated, including records with NULL values. In other words, the query will only return records where the tables match.

You might change the WHERE clause to read WHERE (P.arearec = 1 Or P.arearec Is Null) And (I.arearec = 1 Or I.arearec Is Null). You may need to rearrange the WHERE clause to get the desired result but you must account for the <b>NULL values</b>.