Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Database Query for Products, Customers

Posted on 2014-01-16
5
Medium Priority
?
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 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 39

Assisted Solution

by:PatHartman
PatHartman earned 900 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 35

Accepted Solution

by:
johnsone earned 800 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 39

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 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