If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query with subquery does not take all rows it should i think
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 |
#2
|
|||
|
|||
query with subquery does not take all rows it should i think
|
#4
|
|||
|
|||
query with subquery does not take all rows it should i think
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) "Gary Walter" wrote: 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 "Gary Walter" erroneously wrote: wrote: 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 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) |
#5
|
|||
|
|||
query with subquery does not take all rows it should i think
On 27 Lut, 14:40, "Gary Walter" wrote:
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 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 |
#6
|
|||
|
|||
query with subquery does not take all rows it should i think
On 27 Lut, 15:28, "Gary Walter" wrote:
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) 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 |
#7
|
|||
|
|||
query with subquery does not take all rows it should i think
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 |
#8
|
|||
|
|||
query with subquery does not take all rows it should i think
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 Thats right!!! But how ????? |
#9
|
|||
|
|||
query with subquery does not take all rows it should i think
wrote: 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 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) |
#10
|
|||
|
|||
query with subquery does not take all rows it should i think
On 27 Lut, 17:23, "Gary Walter" wrote:
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 - 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 |
|
Thread Tools | |
Display Modes | |
|
|