Optimize query

[Aldert]

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:

Select * From db 
Where product in (
        Select * from db 
        where insertdate 
            between (
                Select insertdate from db 
                where product = ‘JAM’ 
                and source = ‘Cash_1’
            ) 
            and (
                (Select insertdate from db 
                 where product = ‘JAM’ 
                 and source = ‘Cash_1’
                ) - (sysdate-0.000347)
            )
      ) 
and product in (
        Select * from db 
        where insertdate 
            between (
                Select insertdate from db 
                where product = ‘JAM’ 
                and source = ‘Cash_2’
            ) 
            and (
                (Select insertdate from db 
                 where product = ‘JAM’ 
                 and source = ‘Cash_2’
                ) - (sysdate-0.000347)
            )
    ) 
And product in (
        Select * from db 
        where insertdate 
            between (
                Select insertdate from db 
                where product = ‘JAM’ 
                and source = ‘Cash_3’
            ) 
            and (
              (Select insertdate from db 
               where product = ‘JAM’ 
               and source = ‘Cash_3’
              ) - (sysdate-0.000347)
            )
    )

Who can help me further? I like to hear from you.

Aldert

[Annalyzer]

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.