473,321 Members | 1,877 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Optimize query

2
Hello people,

I have a complex question.

Imagine a supermarket where all sold products are written in a database. The sources are three cash registers. The database will continue filled with new data. I will:
- From a certain product (for example, "JAM", this product is part of a table 'products' in which these specific products are named) the data request which is received in a 30 second after the insertime product 'JAM'.
- The above action I break per cash register (cash_1, Cash_2, Cash_3).
- I want to select all products in all three subqueries occur.

As far as I have:
Expand|Select|Wrap|Line Numbers
  1. Select * From db 
  2. Where product in (
  3.         Select * from db 
  4.         where insertdate 
  5.             between (
  6.                 Select insertdate from db 
  7.                 where product = ‘JAM’ 
  8.                 and source = ‘Cash_1’
  9.             ) 
  10.             and (
  11.                 (Select insertdate from db 
  12.                  where product = ‘JAM’ 
  13.                  and source = ‘Cash_1’
  14.                 ) - (sysdate-0.000347)
  15.             )
  16.       ) 
  17. and product in (
  18.         Select * from db 
  19.         where insertdate 
  20.             between (
  21.                 Select insertdate from db 
  22.                 where product = ‘JAM’ 
  23.                 and source = ‘Cash_2’
  24.             ) 
  25.             and (
  26.                 (Select insertdate from db 
  27.                  where product = ‘JAM’ 
  28.                  and source = ‘Cash_2’
  29.                 ) - (sysdate-0.000347)
  30.             )
  31.     ) 
  32. And product in (
  33.         Select * from db 
  34.         where insertdate 
  35.             between (
  36.                 Select insertdate from db 
  37.                 where product = ‘JAM’ 
  38.                 and source = ‘Cash_3’
  39.             ) 
  40.             and (
  41.               (Select insertdate from db 
  42.                where product = ‘JAM’ 
  43.                and source = ‘Cash_3’
  44.               ) - (sysdate-0.000347)
  45.             )
  46.     )
Who can help me further? I like to hear from you.

Aldert
Jun 29 '09 #1
1 1607
Annalyzer
122 100+
I think I understand what you're trying to do. You want the user to enter "JAM" and then have the query retrieve all occurences of the product "JAM" regardless of the source - Cash_1, Cash_2, or Cash_3. Is that correct?

If this is correct, you don't need such a long complicated query. First, look at line 1 of your query. You're telling the database to retrieve everything from 'db' but you said your table name was 'products.'

If the only possibilites for the source are Cash_1, Cash_2, and Cash_3, then you don't need to specify that at all.

SELECT * FROM products WHERE product = 'JAM'

If there are other sources - Cash_4 and Cash_5, but you just want the ones sold from Cash_1, Cash_2, and Cash_3, then you can use "OR" in your select statement:

SELECT * FROM products WHERE product = 'JAM' AND (source = 'Cash_1' OR source = 'Cash_2' OR source = 'Cash_3')

I hope this helps.
Jul 4 '09 #2

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

Similar topics

0
by: Andreas Falck | last post by:
Hi, I ran the code below on two different versions, 4.0.14 and 4.0.16 respectively, both running RH 7.3 on intel. In version mysql server version 4.0.14 the SELECT privelege suffices for...
6
by: Bruce D | last post by:
Could someone please help to explain why the following query isn't using the index... explain select id from kbm where state = 'MA' table type possible_keys key key_len ref rows Extra...
3
by: Paul Janssen | last post by:
Hello! Can anyone help me out with the following situation: (a) a single query with 550 id's in the IN-clause resulting into 800+ seconds; (b) 550 queries with a single id in the IN-clause...
5
by: xeqister | last post by:
Greetings all, We have a complicated statement in DB2 which takes long hour to complete and we have created most of the indexes. Does anybody knows how to tune the following statement to optimize...
3
by: Reddy | last post by:
The sql query for my datagrid returns 100, 000 records. But the datagrid should display 20 records per page. I am using datagrid paging, but it is taking too much time for the page to load. Is...
4
by: Huaer.XC | last post by:
>From the following MySQL command: EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name = t1.name WHERE t1.id IN(123, 124); which result is:...
13
by: Frank Swarbrick | last post by:
IBM has a product for the VSE operating system called the VSAM Redirector. It allows you to use VSAM to access RDBMS tables/views as if they were actual VSAM files. We're doing a comparison right...
11
by: bravo | last post by:
hi i need to optimize the join query, which joins three tables say table1 ,table2 , table3 each having huge volume of records... the query is as select table1.id,table2.time,table3.Status from...
1
by: acornejo | last post by:
Hi All I've the following code I need to optimize. Currently tblOutgoing is about 250K registers and growing at a rate of about 20k records per day. This code takes me over 5 secs to run on each...
3
zabsmarty
by: zabsmarty | last post by:
Can any one help me to make my query code optimize and load faster. Please help me or any example what steps should we use to optimize. Thank You
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.