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

Count occurance only if tbl1.F1 is in tbl2.F1

aas4mis
P: 97
I've got a field of items (73,000) in Table1, not unique. Each occurrence of the item is a shipment. I can group by item and count each occurrence with no problem. The problem is when I only want to view items if they exist in Table2 (150,000+ records). I believe my code is correct but the query takes way too long. Is there a faster way of getting my results? Here is my code:
Expand|Select|Wrap|Line Numbers
  1. SELECT First(aasmn.item) AS MyItem, Count(aasmn.item) AS "ShipmentQQ"
  2. FROM aasmn
  3. WHERE aasmn.item in (select aasrugs.item from aasrugs)
  4. GROUP BY "MyItem"
  5. ORDER BY "ShipmentQQ" DESC




Thanks
Jan 23 '09 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
I'm not sure, but if you have a lot of duplicates, then perhaps it would help if you change
select aasurgs.item from aasrugs
to
select distinct aasurgs.item from aasrugs
Jan 23 '09 #2

aas4mis
P: 97
@ChipR
Thanks for the tip, but unfortunately aasrugs has no duplicates.
Jan 23 '09 #3

Expert 100+
P: 1,287
Just in case: make sure both tables have indices on the item field. This will make a huge difference.

Also, I'm not sure if EXISTS is better than IN, but I have a hunch that it might be. Something like:

Expand|Select|Wrap|Line Numbers
  1. SELECT First(aasmn.item) AS MyItem, Count(aasmn.item) AS "ShipmentQQ" 
  2. FROM aasmn 
  3. WHERE EXISTS (SELECT * from aasrugs WHERE aasurgs.item = aasmn.item) 
  4. GROUP BY "MyItem" 
  5. ORDER BY "ShipmentQQ" DESC
Jan 23 '09 #4

aas4mis
P: 97
@ChipR
Thanks for all the help. In this case I just needed the raw data, it wasn't for a form or anything that will be used again. I went ahead and just used a temp table for my first query and joined aasrugs.item with my results... a lot faster. BTW, I tried using EXISTS and that didn't seem to speed it up any. FYI, I did not have the fields indexed, that may have helped. Due to time constraints I'll put this one off 'till I need it again. Thanks again.
Jan 23 '09 #5

NeoPa
Expert Mod 15k+
P: 31,411
Use an INNER JOIN rather than trying to duplicate the effect in the WHERE clause.
Expand|Select|Wrap|Line Numbers
  1. SELECT aasmn.item AS MyItem,
  2.        Count(aasmn.item) AS ShipmentQQ
  3.  
  4. FROM [aasmn] INNER JOIN [aasrugs]
  5.   ON aasmn.Item=aasrugs.Item
  6.  
  7. GROUP BY aasmn.Item
  8.  
  9. ORDER BY ShipmentQQ DESC
I'm assuming here that [aasrugs] has no duplicates in its [Item] field.
Jan 25 '09 #6

aas4mis
P: 97
Thanks Neo. I'll have to give that a shot next time, and I'm sure there will be a next time. I'ved used joins countless times in the past for this situation.. no idea why I was going at it with a WHERE clause...one of those days. Oh, and your assumption would be correct, aasrugs.item is distinct by nature.
Jan 26 '09 #7

NeoPa
Expert Mod 15k+
P: 31,411
@ChipR
No problems :)

PS. Quoted point above is also very important ;)
Jan 26 '09 #8

aas4mis
P: 97
I'll keep that in mind, Thanks Neo and Chip
Jan 26 '09 #9

Post your reply

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