Solved

Database Query for Products, Customers

Posted on 2014-01-16
5
304 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 73

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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