By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,702 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,702 IT Pros & Developers. It's quick & easy.

My query has duplicate rows and runs forever

P: 3
I need to calculate which products were purchased by a specific customer in 2014, using Microsoft SQL Server Management Studio 2008.

I am trying to combine data from 3 semi-related tables:
Customer_List
Product_List
Product_Sales

The desired result is as follows:
5 Columns

Expand|Select|Wrap|Line Numbers
  1. Customer_Name Product_Name Product_IDNumber Sales Quantity
  2. ============= ============ ================ ===== ========
  3. Store 1           Product 1   Product ID #1    $0.00     5
  4. Store 1           Product 2   Product ID #2    $1.00     3
  5. Store 1           Product 3   Product ID #3    $2.00     8
  6. Store 2           Product 1   Product ID #1    $2.00     2
  7. Store 2           Product 2   Product ID #2    $1.00     4
  8. Store 2           Product 3   Product ID #3    $5.00     7
  9.  
The current query set-up:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. Product_List.Product_Name
  3. Product_List.Product_IDnumber
  4. Product_Sales.Sales
  5. Product_Sales.Quantity
  6. FROM Customer_List,Product_List,Product_Sales
  7. WHERE Customer_List.RandomID = Product_Sales.RandomID AND
  8. Product_Sales.Code00 = Product_List.Code00 AND
  9. Customer_List.Customer_Name like '%Acme%' AND
  10. Product Sales.Year = 2014
  11. Order by Customer_List.Customer_Name
The query results in the following:
Store 1 Product 1 Product ID #1 $0.00 5
Store 1 Product 2 Product ID #2 $0.00 5
Store 1 Product 3 Product ID #3 $0.00 5


I ran it last night at 5pm. This morning, the query was still executing.
There is not that much data for the query to run that long.

I am new to SQL.

Unfortunately, I cannot be more specific about the data; however, this example accurately describes what I am trying to accomplish.

Any help is greatly appreciated!
Jun 2 '15 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Please use code tags when posting code or formatted data.

A few questions:
  • What is RandomID?
  • What is Code00?
  • Is that the actual query? Because it should error out. You have the table name spelled wrong on line 10. It would be better if you did a copy and paste of the SQL rather than retyping it.
  • Aside from the like operator, there's nothing that should slow it down too much. Even the like operator doesn't slow it down that much.
  • How much data is in each of those tables?
  • Are there any locks on those tables? If you are okay with dirty reads, use a with(nolock) so you can get the data even if the table is locked.
Jun 2 '15 #2

P: 3
Thanks for the response, Rabbit. Sorry about editing errors.
RandomID and Code00 are columns I included to link the tables, but I don't want them in the query results. Was that unnecessary?
What are table locks? Do I insert the phrase "with(nolock)" into the query?
Jun 2 '15 #3

Rabbit
Expert Mod 10K+
P: 12,430
They're necessary, just wanted to make sure they're correct. You put the with(nolock) after a table name if you want to bypass locks on that table and don't mind reading slightly older data.
Jun 2 '15 #4

P: 3
Rabbit, I got help from a colleague. My script was using the wrong data. Once the correct tables were linked, correctly, the problem was solved. Thanks for your time.
Jun 3 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.