Question related to date and join

I'm working on a project where I'm trying to combine three different tables. Table 1 and 2 got a row with the same ID,which in this case is the product_id, and the same goes for table 2 and 3. I'm using date as a condition when I'm sending the query, where I'm only supposed to get the results that fit inside the given frame. Here's my code:

select order.orderDate, orderLine.order_id, product.product_id, product.name
from order, product
inner join orderLine
on product.product_id = orderLine.product_id
where order.orderDate between  '2009-09-01' and '2009-09-30' ;

When I use this, I only开发者_如何学Python get one date, contained in the order-table. I also get info from rows that don't fit the date given.

I'm very sorry if this is nothing but confusing. My english isn't very good, so having a hard time making this understandable. Also, please let me know if you need more information. New to both this site and SQL.

Try joining all three tables:

select o.orderDate, ol.order_id, p.product_id, p.name
from order as o 
inner join orderLine as ol
   on ol.order_id = o.order_id
inner join  product as p on o
   on p.product_id = ol.product_id
where o.orderDate between  '2009-09-01' and '2009-09-30' ;

While I think the missing join clause (as per @p.campbell's answer) will solve your "too many rows returned" problem, I'm wondering if whether there is a further "not enough rows returned" problem with your date representation e.g.

where order.orderDate between '2009-09-01' and '2009-09-30'

The intention is presumably to return orders for September 2009 but would omit one where orderDate = '2009-09-01 12:00:00'.

Your code implies you are using what the literature calls closed-closed representation of periods, where the end date is included in the period. However, the recommendation is use closed-open representation (also known as half open intervals) where the end date is not included in the period.

Thus, using closed-open representation, the same predicate (i.e. all September 2009 orders) would be:

where order.orderDate >= '2009-09-01' 
      and order.orderDate < '2009-10-01'

For more details, see Snodgrass, Richard T. (1999). Developing Time-Oriented Database Applications in SQL, chapter 4 Periods.