I am running a query similar to the one below, and it is not providing the information that is needed.
select
tdate
,store
,register
,cashier
from table1
where exists (select 'x'
from table2
where table2.tdate = table1.tdate
and table2.cashier = table1.cashier
and table2.store = table1.store
and not exists (select 'x'
from table3
where table3.tdate = table2.tdate
and table3.cashier = table2.cashier
and table3.store = table2.store
and table1.ttime between table3.time1 and table3.time2 )
group by table2.tdate, table2.store, table2.cashier
having count(*) = 1 )
I am able to get the correct information by using a different query that is listed below, however I would like to understand why the above does not work. Can anyone shed some light on this subject for me?
select
tdate
,store
,register
,cashier
from table1
where exists (select 'x'
from (select tdate, cashier, store, count(*)
from table2
where not exists (select 'x'
from table3
where table3.tdate = table2.tdate
and table3.cashier = table2.cashier
and table3.store = table2.store
and table1.ttime between table3.time1 and table3.time2 )
group by tdate, cashier, store
having count(*) = 1 ) subquery1
where subquery1.tdate = table1.tdate
and subquery1.store = table1.store
and subquery1.cashier = table1.cashier )
Thank You,
cfw2
select
tdate
,store
,register
,cashier
from table1
where exists (select 'x'
from table2
where table2.tdate = table1.tdate
and table2.cashier = table1.cashier
and table2.store = table1.store
and not exists (select 'x'
from table3
where table3.tdate = table2.tdate
and table3.cashier = table2.cashier
and table3.store = table2.store
and table1.ttime between table3.time1 and table3.time2 )
group by table2.tdate, table2.store, table2.cashier
having count(*) = 1 )
I am able to get the correct information by using a different query that is listed below, however I would like to understand why the above does not work. Can anyone shed some light on this subject for me?
select
tdate
,store
,register
,cashier
from table1
where exists (select 'x'
from (select tdate, cashier, store, count(*)
from table2
where not exists (select 'x'
from table3
where table3.tdate = table2.tdate
and table3.cashier = table2.cashier
and table3.store = table2.store
and table1.ttime between table3.time1 and table3.time2 )
group by tdate, cashier, store
having count(*) = 1 ) subquery1
where subquery1.tdate = table1.tdate
and subquery1.store = table1.store
and subquery1.cashier = table1.cashier )
Thank You,
cfw2