Create SQL String Dealing with Many to Many Relationship

Posted on 2009-05-24
Medium Priority
Last Modified: 2012-08-14
Hello All,
Please see the picture for an overview of the small database I'm working with.  The following SQL string will get me the order ID, customer name, salesperson name, and shipping method for a specified salesperson and shipping method.

SELECT OrderInfo.ID, CustomerInfo.Name, SalesPerson.Name, ShipperInfo.Name
FROM ShipperInfo INNER JOIN (SalesPerson INNER JOIN (CustomerInfo INNER JOIN OrderInfo ON CustomerInfo.ID = OrderInfo.CustomerID) ON SalesPerson.ID = OrderInfo.SalesPersonID) ON ShipperInfo.ID = OrderInfo.ShipperID
WHERE SalesPerson.Name="Andrew Fuller" and shipperinfo.name = "United Package";

I need to be able to filter this data to only include orders which contained a specific product.  For example, along with the other WHERE criteria only include orders where one of the items ordered was Boston Crab Meat (i.e. ProductInfo.Name = "Boston Crab Meat")

Thanks a million

Question by:kgerb

Accepted Solution

shambalad earned 2000 total points
ID: 24464426
Try This:

PARAMETERS SalesPerson Text ( 255 ), Shipper Text ( 255 ), Product Text ( 255 );
SELECT OrderInfo.ID, CustomerInfo.Name, SalesPerson.Name, ShipperInfo.Name, ProductInfo.Name, Orders.Quantity, Orders.ExtendedPrice
FROM ProductInfo INNER JOIN ((SalesPerson INNER JOIN (ShipperInfo INNER JOIN (CustomerInfo INNER JOIN OrderInfo ON CustomerInfo.ID = OrderInfo.CustomerID) ON ShipperInfo.ShipperID = OrderInfo.ShipperID) ON SalesPerson.SalesPersonID = OrderInfo.SalesPersonID) INNER JOIN Orders ON OrderInfo.ID = Orders.OrderID) ON ProductInfo.ProductID = Orders.ProductID
WHERE (((SalesPerson.Name)=[SalesPerson]) AND ((ShipperInfo.Name)=[Shipper]) AND ((ProductInfo.Name)=[Product]));
LVL 12

Author Comment

ID: 24464458
Thanks shambalad,
That will work nicely.  I've been starting at INNER JOINS for hours now.  I think my head is about to start spinning:).  Thanks again.


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question