CODEWITHSUNDEEP
ms-access
Elie
Elie

Reputation: 21

Sql query in Microsoft Access

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.

Upvotes: 0

Views: 287

Answers (1)

Elie
Elie

Reputation: 21

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.

Upvotes: 1

Related Questions