?
Solved

Database Query for Products, Customers

Posted on 2014-01-16
5
Medium Priority
?
322 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 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 40

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 40

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

862 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