SQL Inner Join Help

Results 1 to 2 of 2

Thread: SQL Inner Join Help

  1. #1
    Join Date
    Dec 1969

    Default SQL Inner Join Help

    Hi Everyone!<BR><BR>I am trying to build a simple eProceurement site and am having a lot of trouble consolidating a bunch of tables I have designed in Access. <BR><BR>I have 4 tables named: Bundles, Products, ProductBundles, and Orders. <BR><BR>"Bundles" is composed of 2 columms: BundleID and BundleDescription<BR>ex: <BR>BundleID BundleDescription<BR> 1 OKI Printer<BR> 2 HP Printer<BR><BR>"Products" Table is composed of 3 columns: ProductID, productDescription, and Price<BR>ex: <BR>ProductID ProductDescription Price<BR> 1 Printer $300 <BR> 2 Cord $12<BR> 3 Paper $50<BR><BR>Both "Bundles" and "Products" tables are prefilled with data. While the next 2 tables are inserted with data as clients order. <BR><BR>Clients are supposed to click on a respective "bundle" and what "products" they want to be associated with that bundle. Once the hit submit, their specific order is consolidated into another table called "productBundles". I am assumming you do this with a SQL select and insert but i am having trouble doing so. This is my first problem. (Problem#1)<BR><BR>The structure of the "consolidating" productBundles table would look something like the following.<BR>ex: <BR>BundleID ProductID<BR> 1 1<BR> 1 2<BR> 1 3<BR><BR>This will associate a specifc bundle with the specific products a client has chosen to encompass the respective bundle. <BR><BR>Now here is where I am really having trouble. I would like to use the information inserted into the "productBundles" table to also fill a table called "Orders".<BR><BR>The Orders table will have an OrderID(PK), BundleID, Product, and Price column.<BR><BR>The Orders table will look something like:<BR><BR>OrderID(PK) BundleID Product Price<BR> 1 1 1 $100<BR> 1 1 2 $12<BR> 1 1 3 $50<BR><BR>Do I do this via an inner join? I am having trouble thinking of a way to associate a specific order from one table with data from another table to put into my "Orders" table (Problem#2). <BR><BR>Would I use an inner join here?? Or is there some other code for SQL I could use? I am at a total loss here. <BR><BR>If someone has any clue as to how to solve my Problem#1 and Problem#2 I would be indebted to you. I have been working on this for more than a week and a half and am losing my hair literally. I am sure there is something logical that I am missing but if any one could help me with some code that would be great. <BR><BR>Thanks in advanced for any help!!!!!!!!!!!!!!!!!!!!!!!!<BR><BR>Colin <BR>

  2. #2
    Join Date
    Dec 1969

    Default Too complicated

    I understand the concept of having pre-defined bundles, but what&#039;s the point of allowing a customer to create his/her own bundle? <BR><BR>Do you give them a special discount? Maybe 10%?<BR><BR>Suppose they only choose a box of paper to put in their bundle. Still give them the 10% discount???<BR><BR>Yeah, of course you need inner joins. But first you need to fix your table designs.<BR><BR>How can OrderID be the PK in your orders table when the example you show has the same OrderID used in *three* records??? <BR><BR>You need an Orders table *AND* an OrderItems table.<BR><BR>Rethink your designs before you start coding and I bet it all gets easier.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts