• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Database Query for Products, Customers

Hi All

   I have 4 tables Products, Product_Folder, Folder_User, User

 Product (Product ID, Name, Expiry Timestamp)
 Product Folder (Product Folder ID, Product ID, Folder ID)
 Folder User (Folder_User Id, Folder ID, User ID, Permsn ID)
 User (User ID, Name, Email)

1)  I need to notify all Users of all the expired products?
SELECT Product.Product_Name, User. Name, User.Email FROM Product_Folder
INNER JOIN (SELECT Product_ID from PRODUCT where Expiry_Timestamp <= CURRENT_TIMESTAMP) temp ON Product_Folder.Product_ID = temp.Product_ID
INNER JOIN Folder_User ON Product_Folder.Folder Id = Folder_User.Folder_ID
INNER JOIN User ON Folder_User.User_ID = User.User_id

Please let me know if I am right .. if so can the query be optimized (just to know) ?

I appreciate it!
3 Solutions
the query syntax looks fine for performance

you probably want indexes on the ids (if you don't already have them)
and assuming most of your product is NOT expired, then an index on expiry_timestamp will likely help too
Looking at your foreign keys, there appears to be a missing table.  The join for [Folder User]  to [Product Folder] is not FK to PK.

You also don't need the subquery.  Inner joins across the board will work.  Jet/ACE don't always optimize subqueries very well so try it both ways if this version seems slow.

And finally, it is poor practice to include spaces and special characters in your table and column names.  Since in some cases you've used "_" and in some you've used " ", I don't really know what your actual names are.  Names should include only letters (upper/lower), numbers, and the underscore.
johnsoneSenior Oracle DBACommented:
Removing the subquery, it would look like this

SELECT product.product_name, 
FROM   product 
       INNER JOIN product_folder 
               ON product_folder.product_id = product.product_id 
       INNER JOIN folder_user 
               ON product_folder.folder_id = folder_user.folder_id 
               ON folder_user.user_id = USER.user_id 
WHERE  product.expiry_timestamp <= CURRENT_TIMESTAMP 

Open in new window

To me as long as you have indexes on PRODUCT(EXPIRY_TIMESTAMP), PRODUCT_FOLDER(PRODUCT_ID), FOLDER_USER(FOLDER_ID) and USER(USER_ID) it should be OK.  Those are all your join keys and the PRODUCT table should be the driving table.
If you have defined relationships using the relationship window, Access will already have created indexes for all the foreign keys (they are hidden but you can see them if you open the MSysRelationships table) so you don't need to add additional indexes.  Of course, if you haven't created relationships, why not?

You only need to index the columns that are used for searching that are not foreign keys.
shash_broAuthor Commented:
Thanks much all. I appreciate it greatly.  I did not include all columns, the tables folder_User and Product_User have the relationship via pk fk, and the names are all with underscores but I just didn't type them here.  So I will use all inner joins and add an Unique Index for Expiry timestamp.  Thanks much again.
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now