By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,983 Members | 1,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,983 IT Pros & Developers. It's quick & easy.

Selecting all records from a join, even when the value of one table is null

P: 11
Hi there,

I've been struggling with a query for a while now, and would be grateful if anyone here could help. The database is for an invoicing system, with the following tables & fields involved in said query:

Invoice (id, orderDate)
Product (id, productName)
Invoice_Item (id, invoice_id, product_id, quantity)

Basically, I've been trying to write some SQL which returns a list of all products in the product table along with the total quantities of these products sold (in invoices) between two dates.

So far - my query returns a list of products where total quantity sold is greater than zero, but does not return a complete list of products.

My query so far is below, any help would be greatly appreciated!

Expand|Select|Wrap|Line Numbers
  1. SELECT productName, SUM(quantity) AS quantity
  2. FROM Invoice 
  3. INNER JOIN Invoice_Item ON Invoice.id=Invoice_Item.invoiceRecordID 
  4. WHERE unix_timestamp(orderDate) >= $startDate AND unix_timestamp(orderDate) <= $endDate 
  5. GROUP BY Product.id 
  6.  
Oct 7 '07 #1
Share this Question
Share on Google+
4 Replies


pbmods
Expert 5K+
P: 5,821
Heya, ajcolburn.

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

Datetimes don't have to be converted to Unix timestamps; you can just compare them as you would any other variable.

Incidentally, you never joined Products, so `Products`.`id` doesn't exist to this query. Did you mean to group by `Invoice_Item`.`productID` instead?

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `productName`,
  3.         SUM(`quantity`) AS `quantity`
  4.     FROM
  5.     (
  6.             `Invoice`
  7.         LEFT JOIN
  8.             `Invoice_Item`
  9.                 ON
  10.                     `Invoice`.`id` = `Invoice_Item`.`invoiceRecordID`
  11.     )
  12.     WHERE
  13.         `orderDate`
  14.             BETWEEN
  15.                     `startDate`
  16.                 AND
  17.                     `endDate`
  18.     GROUP BY
  19.         `Invoice_Item`.`productID`
  20.  
Oct 7 '07 #2

P: 11
Thanks pbmods,

That cleared a few things up> You're right - I missed out the Products table, your solution is great but I was trying to list all products from the products table regardless of whether they are related to any Invoice_Item records. So you'd have a complete 'stock list' with sales totals for each product alongside, even if a product's sales total is 0. Could you give any further assistance? Many thanks for your help so far.

Alex.
Oct 7 '07 #3

pbmods
Expert 5K+
P: 5,821
Heya, Alex.

Make `Products` the first table in your join, and it will fetch every row from Products that matches the WHERE clause.
Oct 7 '07 #4

P: 11
This didn't solve the problem of returning all rows from Product table, although the guide below helped me write the correct query in the end:

SQL Group By Techniques

My final query:

Expand|Select|Wrap|Line Numbers
  1. SELECT P.productName, P.id, SW.wholesaleUnits, SW.wholesaleOrders
  2. FROM Product P
  3. LEFT JOIN 
  4. (SELECT productID, SUM(quantity) as wholesaleUnits, COUNT(Invoice.id) AS wholesaleOrders 
  5. FROM Invoice_Item 
  6. INNER JOIN Invoice 
  7. ON Invoice_Item.invoiceRecordID=Invoice.id 
  8. WHERE orderDate BETWEEN $startDate AND $endDate    
  9. GROUP BY productID) SW
  10. ON P.id=SW.productID 
  11.  
Oct 8 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.