If there is only one partNumber then I believe this one will work: select ProductsOverride.Cost from Products, ProductsOverride where Products.PartNumber = 'form.PartNumber' and Products.PartNumber = ProductsOverride.PartNumber
If there are multiple partNumber for one code then try this one joining all three tables: select ProductsOverride.Cost from Products, ProductsOverride, ManufacturerCodes where Products.PartNumber = 'form.PartNumber' and Products.PartNumber = ProductsOverride.PartNumber and ProductsOverride.ManufacturerCode = ManufacturerCodes.Code ----- Original Message ----- From: "Bradford T Comer" <[EMAIL PROTECTED]> To: "SQL" <[email protected]> Sent: Monday, April 25, 2005 2:08 AM Subject: Query Help >I have 3 tables in my clients SQL Server 2k instance: > >> Products > :Columns of importance: > Products.Manufacturer > Products.PartNumber >> ProductsOverride > :Columns of importance: > ProductsOverride.ManufacturerCode > ProductsOverride.Cost > ProductsOverride.PartNumber >> ManufacturerCodes > :Columns of importance: > ManufacturerCodes.Code > ManufacturerCodes.Manufacturer > > Somehow I have to retrieve the ProductsOverride.Cost value for the given > PartNumber (which is entered in a search form field), using > the Products.Manufacturer column against the > ManufacturerCodes.Manufacturer to get the ProductsOverride.Cost. The > correlation to > ProductsOverride is the ManufacturerCodes.Code and the > ProductsOverride.ManufacturerCode. > > Gosh I just confused myself more. > > Anyone have any ideas? If I am not being clear, which I am almost positive > I am not, please let me know what I can do to work thru > this. > > Thanks, > Brad > > > > > -----Original Message----- > From: Jeffry Houser [mailto:[EMAIL PROTECTED] > Sent: Friday, April 22, 2005 2:49 PM > To: SQL > Subject: Re: speed? > > > :hmm: I was unaware. > > In that case, why use views at all? > > At 04:08 PM 4/22/2005, you wrote: >>Views are slower than regular lookups - only slightly but enough to >>notice. >> >> >>Quoting Jeffry Houser <[EMAIL PROTECTED]>: >> >> > I think you said SQL Server, so why not create a view that >> > pre-selects >> > those 7 columns? I suspect that will alleviate any performance >> > concerns >> > you may have. >> > >> > Generally when developing databases, I try to keep database design to >> > what is "logically" correct. So, a table w/ 82 columns does not >> inherently >> > mean (to me) that the design is bad. >> > >> > >> > At 10:41 AM 4/22/2005, you wrote: >> > >So to sum up, the rows would effect the speed, but not the columns. >> > >Is >> > >that it? >> > > >> > >The reason I asked is because we have one table of 82 columns and we >> > >only >> > >work with 7 columns. To me that is just bad database design. So I >> thought >> > >it would be better if we split the table into two: one with the 7 >> > >columns >> > >we need and the other with 75 columns we don't every query, but since >> > >speed is not the factor, I don't really have good reasons to ask my >> > >boss >> > >to split it. >> > > >> > >Johnny >> > > >> > > >> > > > As with many things in the DBMS world, the answer is, "Maybe. It >> > > > depends." >> > > > The primary things that will affect your performance are the row >> > > > size, >> > > > the >> > > > datatypes involved, and the indexing you have in place. Secondary >> > > > to >> > > > that >> > > > will be hardware - things like the amount of memory and type of >> > > > disk >> > > > storage. However, I doubt very much that you will see a >> > > > performance >> > > > difference of 2x no matter what you try. >> > > > >> > > > -- >> > > > Eric >> > > > _____________________ >> > > > "Once the game is over, the King and the pawn go back in the same >> > > > box. >> > > > " >> > > > -- Italian proverb >> > > > >> > > > >> > > > >> > > >> > > >> > > > >> > > >> > > > "Johnny >> > > Le" >> > > >> > > > >> > > >> > > > <[EMAIL PROTECTED] To: "SQL" >> > > <[email protected]> >> > > > >> > > >> > > > > cc: >> > > >> > > > >> > > >> > > > Subject: speed? >> > > >> > > > 04/22/2005 >> > > 09:53 >> > > > >> > > >> > > > >> > > AM >> > > >> > > > >> > > >> > > > Please respond >> > > to >> > > > >> > > >> > > > >> > > sql >> > > >> > > > >> > > >> > > >> > > > >> > > >> > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > > > Hi, >> > > > >> > > > Does the number of columns in a table effect the performance? For >> > > > example, >> > > > I want to select 7 fields and 100 rows of a 7 column table with >> > >> > > > millions of >> > > > rows. Will that be faster than if I select 7 fields and 100 rows >> > > > of >> > > > an 82 >> > > > column table with millions of rows? So 7 vs. 82 columns. All >> > > > other >> > > > factors remain the same. I am using MS SQL Server 2000. I would >> > > > think it >> > > > is faster. What do you think? If it is indeed faster, how much >> > > > faster >> > > > are >> > > > we talking about here? double or tripple the speed? >> > > > >> > > > Johnny >> > > > >> > > >> > > >> > >> > >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2248 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
