Solved

Database Query for Products, Customers

Posted on 2014-01-16
5
308 Views
Last Modified: 2014-01-16
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
Go

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

I appreciate it!
0
Comment
Question by:shash_bro
5 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 75 total points
ID: 39785336
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
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 225 total points
ID: 39785403
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.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 200 total points
ID: 39785553
Removing the subquery, it would look like this

SELECT product.product_name, 
       USER.name, 
       USER.email 
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 
       INNER JOIN USER 
               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.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39785609
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.
0
 

Author Closing Comment

by:shash_bro
ID: 39786477
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.
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

808 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