query with subquery does not take all rows it should i think

P

pat12

Hello


There is two tables:
1.
product_nb;price_start_date;shop;price

2.
Days (dates from two months day par day)

I try to get the info about the price for each shop and each product
for each day from second table

Here is my query:
Select a.product_nb,b.days,a.price_start_date,a.shop, a.price
from table1 as a,table2 as b
where a.price_start_date=(select max(c.price_start_date) from table1
as c where a.shop=c.shop and a.product_nb=c.product_nb and
a.price_start_date<=b.days)

It gives me data but not for all days from table2

Where I went wrong? I totally confused how the subquery works now

Any suggestion appreciated
PAT
 
G

Gary Walter

There is two tables:
1.
product_nb;price_start_date;shop;price

2.
Days (dates from two months day par day)

I try to get the info about the price for each shop and each product
for each day from second table

Here is my query:
Select a.product_nb,b.days,a.price_start_date,a.shop, a.price
from table1 as a,table2 as b
where a.price_start_date=(select max(c.price_start_date) from table1
as c where a.shop=c.shop and a.product_nb=c.product_nb and
a.price_start_date<=b.days)

It gives me data but not for all days from table2

Where I went wrong? I totally confused how the subquery works now
Click to expand...
I don't know for sure, but I think you want

Select
a.product_nb,
b.days,
a.price_start_date,
a.shop,
a.price
from table1 as a,
table2 as b
LEFT JOIN
table1 AS a
ON
b.Days = a.price_start_date
where
a.price_start_date=
(select
max(c.price_start_date)
from
table2 As d
INNER JOIN
table1 as c
ON
d.Days = c.price_start_date
where
c.shop=a.shop
and
c.product_nb=a.product_nb)
 
G

Gary Walter

What was I thinking...you can't get all days
with LEFT JOIN while filtering on the inner
table. Argh...

Divide-and-conquer

Q1: 'first filter inner table

Select
a.product_nb,
a.price_start_date,
a.shop,
a.price
from
table1 AS a
where
a.price_start_date=
(select
max(c.price_start_date)
from
table2 As d
INNER JOIN
table1 as c
ON
d.Days = c.price_start_date
where
c.shop=a.shop
and
c.product_nb=a.product_nb)

final query:

Select
a.product_nb,
b.days,
a.price_start_date,
a.shop,
a.price
from
table2 as b
LEFT JOIN
Q1 AS a
ON
b.price_start_date = a.price_start_date
 
G

Gary Walter

just to be sure we are on same page...

in Q1, what is subquery doing?

(select
max(c.price_start_date)
from
table2 As d
INNER JOIN
table1 as c
ON
d.Days = c.price_start_date
where
c.shop=a.shop
and
c.product_nb=a.product_nb)

it is saying... for a specific shop
and product_nb in the outer query,
what was the latest price_start_date
in the records in table1 for that specific
shop and product_nb that occur
in the "time span" of table2.

What if there was no price_start_date
for a specific shop and product_nb
in that "time span?" Do you want to
go further back in time than this "time span?"

Possibly you could change to a non-equi join...

(select
max(c.price_start_date)
from
table2 As d
INNER JOIN
table1 as c
ON
c.price_start_date <= d.Days
where
c.shop=a.shop
and
c.product_nb=a.product_nb)
 
P

pat12

What was I thinking...you can't get all days
with LEFT JOIN while filtering on the inner
table. Argh...

Divide-and-conquer

Q1: 'first filter inner table

Select
a.product_nb,
a.price_start_date,
a.shop,
a.price
from
table1 AS a
where
a.price_start_date=
(select
max(c.price_start_date)
from
table2 As d
INNER JOIN
table1 as c
ON
d.Days = c.price_start_date
where
c.shop=a.shop
and
c.product_nb=a.product_nb)

final query:

Select
a.product_nb,
b.days,
a.price_start_date,
a.shop,
a.price
from
table2 as b
LEFT JOIN
Q1 AS a
ON
b.price_start_date = a.price_start_date
Click to expand...


Thanks for quick reply
Unfortunately it does'nt work. I mean the Q1. The problem is that days
of setting the price (price_start_date ) can be completely different
from Day. Q1 takes tha max day for shop and product without checking
Days (dont know why) I see in Q1 the price_star_date for shop nb2 =
2006-09-13 when for this shop table1 has two price_start_dates
2006-05-12 and2006-09-13 and for Days <2006-09-13 price_start_date
2006-05-12 should be taken.

PAT
 
P

pat12

just to be sure we are on same page...

in Q1, what is subquery doing?

(select
max(c.price_start_date)
from
table2 As d
INNER JOIN
table1 as c
ON
d.Days = c.price_start_date
where
c.shop=a.shop
and
c.product_nb=a.product_nb)

it is saying... for a specific shop
and product_nb in the outer query,
what was the latest price_start_date
in the records in table1 for that specific
shop and product_nb that occur
in the "time span" of table2.

What if there was no price_start_date
for a specific shop and product_nb
in that "time span?" Do you want to
go further back in time than this "time span?"

Possibly you could change to a non-equi join...

(select
max(c.price_start_date)
from
table2 As d
INNER JOIN
table1 as c
ON
c.price_start_date <= d.Days
where
c.shop=a.shop
and
c.product_nb=a.product_nb)
Click to expand...


I did.

I really don't know why it isn't working (hope it is not some stupid
mistake since I am sitting with this for few hours)

I changed c.price_start_date = d.Days for c.price_start_date = d.Days
and nothing happens
The price for the shop 2 in table 1 was set two times (in fact three
but the fist one is not important) 2006-05-12 and 2006-09-13 and both
queries takes only 2006-09-13.

Could you spend for me 5 more minutes. Below is the table1 and table2
I work with. Could you please run query on these tables (you can see
shop nb2 has few dates and only one is taken in my Access
Thanks
PAT (going crazy )

TABLE1
product_nb price_start_date shop price
61864 2006-09-11 12 1,69
61864 2006-09-13 12 1,69
61864 2006-08-02 12 1,65
61864 2001-05-17 12 1,75
61864 2006-09-13 13 1,69
61864 2006-05-12 13 1,6
61864 2001-05-17 13 1,75
61864 2006-09-13 14 1,69
61864 2006-05-12 14 1,6
61864 2001-05-17 14 1,75
61864 2006-09-13 15 1,69
61864 2006-08-04 15 1,65
61864 2001-05-17 15 1,75
61864 2006-09-12 16 1,69
61864 2006-09-13 16 1,69
61864 2006-08-09 16 1,65
61864 2001-05-17 16 1,75
61864 2006-09-13 17 1,69
61864 2006-08-11 17 1,59
61864 2001-05-17 17 1,75
61864 2006-09-11 18 1,69
61864 2006-09-13 18 1,69
61864 2006-07-12 18 1,65
61864 2001-05-17 18 1,75
61864 2006-09-08 19 1,69
61864 2006-09-13 19 1,69
61864 2006-08-02 19 1,65
61864 2001-05-17 19 1,75
61864 2006-09-13 20 1,69
61864 2006-05-12 20 1,6
61864 2001-05-17 20 1,75
61864 2006-09-13 701 1,69
61864 2001-05-17 701 1,75
61864 2006-09-13 2 1,69
61864 2006-05-12 2 1,6
61864 2001-05-17 2 1,75
61864 2006-09-13 3 1,69
61864 2006-08-14 3 1,65
61864 2001-05-17 3 1,75
61864 2006-09-11 4 1,69
61864 2006-09-13 4 1,69
61864 2006-07-11 4 1,65
61864 2001-05-17 4 1,75
61864 2006-09-12 5 1,59
61864 2006-09-13 5 1,69
61864 2006-08-04 5 1,69
61864 2001-05-17 5 1,75
61864 2006-09-13 6 1,69
61864 2006-05-12 6 1,6
61864 2001-05-17 6 1,75
61864 2006-09-13 7 1,69
61864 2006-05-12 7 1,6
61864 2001-05-17 7 1,75
61864 2006-09-13 8 1,69
61864 2006-05-12 8 1,6
61864 2001-05-17 8 1,75
61864 2006-09-12 9 1,69
61864 2006-09-13 9 1,69
61864 2006-07-11 9 1,65
61864 2001-05-17 9 1,75
61864 2006-09-13 10 1,69
61864 2006-05-12 10 1,6
61864 2001-05-17 10 1,75
61864 2006-09-13 11 1,69
61864 2006-08-11 11 1,59
61864 2001-05-17 11 1,75


Days
2006-09-01
2006-09-02
2006-09-03
2006-09-04
2006-09-05
2006-09-06
2006-09-07
2006-09-08
2006-09-09
2006-09-10
2006-09-11
2006-09-12
2006-09-13
2006-09-14
2006-09-15
2006-09-16
2006-09-17
2006-09-18
2006-09-19
2006-09-20
2006-09-21
2006-09-22
2006-09-23
2006-09-24
2006-09-25
2006-09-26
2006-09-27
2006-09-28
2006-09-29
2006-09-30
2006-10-01
2006-10-02
2006-10-03
2006-10-04
2006-10-05
2006-10-06
2006-10-07
2006-10-08
2006-10-09
2006-10-10
2006-10-11
2006-10-12
2006-10-13
2006-10-14
2006-10-15
2006-10-16
2006-10-17
2006-10-18
2006-10-19
2006-10-20
2006-10-21
2006-10-22
2006-10-23
2006-10-24
2006-10-25
2006-10-26
2006-10-27
2006-10-28
2006-10-29
2006-10-30
2006-10-31
 
G

Gary Walter

<I try to get the info about the price for each shop and each product
<for each day from second table

I think I misunderstood before..

For a simple case...

TABLE1
product_nb price_start_date shop price
61864 2006-09-13 2 1,69
61864 2006-05-12 2 1,6
61864 2001-05-17 2 1,75

TABLE2
Days
2006-09-01
2006-09-02
2006-09-03
2006-09-04
2006-09-05
2006-09-06
2006-09-07
2006-09-08
2006-09-09
2006-09-10
2006-09-11
2006-09-12
2006-09-13
2006-09-14
2006-09-15
2006-09-16
2006-09-17

I think the goal is you want to return?
(on 2006-09-13 it changed from 1,6 to 1,69)

Days product_nb price_start_date shop price
2006-09-01 61864 2006-05-12 2 1,6
2006-09-02 61864 2006-05-12 2 1,6
2006-09-03 61864 2006-05-12 2 1,6
2006-09-04 61864 2006-05-12 2 1,6
2006-09-05 61864 2006-05-12 2 1,6
2006-09-06 61864 2006-05-12 2 1,6
2006-09-07 61864 2006-05-12 2 1,6
2006-09-08 61864 2006-05-12 2 1,6
2006-09-09 61864 2006-05-12 2 1,6
2006-09-10 61864 2006-05-12 2 1,6
2006-09-11 61864 2006-05-12 2 1,6
2006-09-12 61864 2006-05-12 2 1,6
2006-09-13 61864 2006-09-13 2 1,69
2006-09-14 61864 2006-09-13 2 1,69
2006-09-15 61864 2006-09-13 2 1,69
2006-09-16 61864 2006-09-13 2 1,69
2006-09-17 61864 2006-09-13 2 1,69
 
P

pat12

I think the goal is you want to return?
(on 2006-09-13 it changed from 1,6 to 1,69)

Days product_nb price_start_date shop price
2006-09-01 61864 2006-05-12 2 1,6
2006-09-02 61864 2006-05-12 2 1,6
2006-09-03 61864 2006-05-12 2 1,6
2006-09-04 61864 2006-05-12 2 1,6
2006-09-05 61864 2006-05-12 2 1,6
2006-09-06 61864 2006-05-12 2 1,6
2006-09-07 61864 2006-05-12 2 1,6
2006-09-08 61864 2006-05-12 2 1,6
2006-09-09 61864 2006-05-12 2 1,6
2006-09-10 61864 2006-05-12 2 1,6
2006-09-11 61864 2006-05-12 2 1,6
2006-09-12 61864 2006-05-12 2 1,6
2006-09-13 61864 2006-09-13 2 1,69
2006-09-14 61864 2006-09-13 2 1,69
2006-09-15 61864 2006-09-13 2 1,69
2006-09-16 61864 2006-09-13 2 1,69
2006-09-17 61864 2006-09-13 2 1,69
Click to expand...

Thats right!!!

But how ?????
 
G

Gary Walter

There is two tables:
1.
product_nb;price_start_date;shop;price

2.
Days (dates from two months day par day)

I try to get the info about the price for each shop and each product
for each day from second table

Here is my query:
Select a.product_nb,b.days,a.price_start_date,a.shop, a.price
from table1 as a,table2 as b
where a.price_start_date=(select max(c.price_start_date) from table1
as c where a.shop=c.shop and a.product_nb=c.product_nb and
a.price_start_date<=b.days)

It gives me data but not for all days from table2

Where I went wrong? I totally confused how the subquery works now

Any suggestion appreciated
PAT
Click to expand...
okay...

change last line to

c.price_start_date<=b.days

i.e.,

Select
a.product_nb,
b.days,
a.price_start_date,
a.shop,
a.price
from
table1 as a, table2 as b
where
a.price_start_date=
(select
max(c.price_start_date)
from table1 as c
where
c.shop=a.shop
and
c.product_nb=a.product_nb
and
c.price_start_date<=b.days)
 
P

pat12

okay...

change last line to

c.price_start_date<=b.days

i.e.,

Select
a.product_nb,
b.days,
a.price_start_date,
a.shop,
a.price
from
table1 as a, table2 as b
where
a.price_start_date=
(select
max(c.price_start_date)
from table1 as c
where
c.shop=a.shop
and
c.product_nb=a.product_nb
and
c.price_start_date<=b.days)- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -
Click to expand...



YYYEEESSSS

You changed one letter :) and its working. It is so logical now. I
was so stupid.
Many many many thanks Gary I was loosing faith in sql instead of
thinking

All the best for you
Patryk
 
G

Gary Walter

okay...

change last line to

c.price_start_date<=b.days

i.e.,

Select
a.product_nb,
b.days,
a.price_start_date,
a.shop,
a.price
from
table1 as a, table2 as b
where
a.price_start_date=
(select
max(c.price_start_date)
from table1 as c
where
c.shop=a.shop
and
c.product_nb=a.product_nb
and
c.price_start_date<=b.days)- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -
Click to expand...
YYYEEESSSS

You changed one letter :) and its working. It is so logical now. I
was so stupid.
Many many many thanks Gary I was loosing faith in sql instead of
thinking

All the best for you
Patryk
Click to expand...

You're welcome Patryk.

I know the feeling.

Old trite American saying:

"Been there, done that."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

crosstab query fails when based on query with subquery? 6
recursively retrieve records from 2nd table based on records from 1
SUBQUERIES in the FROM clause with Access query 7
SQL Statement 2
Simple query w. subquery doesn't work 4
Complex Queries 1
UnMatched Query 2
Query on two tables with matching null fields 4

Top