Sql query in Microsoft Access

Question

I have this query written in Microsoft Access:

SELECT p.artnbr                            AS [Number], 
   p.name                              AS Name, 
   s.sizename                          AS Sizes, 
   s.sizeindex                         AS SizeIndex, 
   s.oid                               AS SizeId, 
   l.name                              AS LocationName, 
   (SELECT od.quantity 
    FROM   orderdetails od 
    WHERE  od.ORDER = (SELECT o.oid 
                       FROM   [order] o 
                       WHERE  o.active = -1 
                              AND o.location = l.oid) 
           AND od.productsize = s.oid) AS Quantity 
FROM   [size] AS s 
   INNER JOIN (product AS p 
               INNER JOIN (favorite AS f 
                           INNER JOIN location AS l 
                                   ON f.customer = l.customer) 
                       ON p.oid = f.product) 
           ON p.oid = s.product 
WHERE  f.customer = @customer

The link below shows tables i used and their relationships. https://dl.dropbox.com/u/18377860/QueryTables.png

This query returns a correct result, but as u can see i am using a Sub query to get the quantity. I cant figure out how can i rewrite this query using join statements instead of that long sub-query. Any help would be appreciated.
Regards

EDIT: To make it more clear, my query needs to get all unique combinations of (product, size, location) whether they have an [Order] or not and display ordered quantity. If an order does not exist for a particular combination the quantity should be null.

EDIT2: I managed to build the query and it looks like this:

SELECT p.ArtNbr                    AS [Number], 
   p.Name                          AS Name, 
   s.SizeName                      AS Sizes, 
   s.SizeIndex                     AS SizeIndex, 
   s.Oid                           AS SizeId, 
   l.Name                          AS LocationName, 
   so.qty                          AS Quantity
FROM ([Size] AS s 
         INNER JOIN (Product AS p 
               INNER JOIN (Favorite AS f 
                           INNER JOIN Location AS l 
                                   ON f.Customer = l.Customer) ON p.OID = f.Product) 
                       ON p.OID = s.Product) 
      LEFT JOIN 
          (SELECT od.ProductSize       AS PS, 
                  od.Quantity          AS qty, 
                  o.Location as Location 
           FROM OrderDetails AS od INNER JOIN [Order] AS o ON od.Order = o.OID 
           WHERE o.Active = -1)  AS so ON so.PS = s.OID
WHERE f.Customer = @customer AND (l.OID = so.Location OR so.Location is null)

I did like Matt said, i moved the sub-query into the FROM section, i included ProductSize and Location in the select query of the derived table, then linking the result with "Size" table and i finally added a condition to the where section to eliminate the repetition of the same record for each Location.

I tried using left joins but it didn't work for me, can you write it for me please. Thanks — Elie
@Elie I suggest moving your "sub-select" into the FROM section, and to include 'location' and 'productsize' as part of a 'derived table', you can then link this back in the same way and performance should be improved. — Matt Donnan
@Matt Thank you for your reply, can you give more details about your solution or a quick example — Elie
Elie, why not try some of the suggestions and post back your attempts? People like to see an effort, rather being asked "do it for me". BTW "did not work" is never enough information. — Fionnuala

Elie Elie · Accepted Answer · 2012-08-30T09:02:11

I managed to build the query and it looks like this:

SELECT p.ArtNbr                    AS [Number], 
   p.Name                          AS Name, 
   s.SizeName                      AS Sizes, 
   s.SizeIndex                     AS SizeIndex, 
   s.Oid                           AS SizeId, 
   l.Name                          AS LocationName, 
   so.qty                          AS Quantity
FROM ([Size] AS s 
         INNER JOIN (Product AS p 
               INNER JOIN (Favorite AS f 
                           INNER JOIN Location AS l 
                                   ON f.Customer = l.Customer) ON p.OID = f.Product) 
                       ON p.OID = s.Product) 
      LEFT JOIN 
          (SELECT od.ProductSize       AS PS, 
                  od.Quantity          AS qty, 
                  o.Location as Location 
           FROM OrderDetails AS od INNER JOIN [Order] AS o ON od.Order = o.OID 
           WHERE o.Active = -1)  AS so ON so.PS = s.OID
WHERE f.Customer = @customer AND (l.OID = so.Location OR so.Location is null)

I did like Matt said, i moved the sub-query into the FROM section, i included ProductSize and Location in the select query of the derived table, then linking the result with "Size" table and i finally added a condition to the where section to eliminate the repetition of the same record for each Location.

Sql query in Microsoft Access

1 Answers