Mitchell Vincent wrote: > I could swear I've done this type of thing before and am sure I'm > overlooking something simple. > > Is this correct syntax? > > SELECT im.invoice_date as invoice_date,im.pay_by as > due_date,im.invoice_id as invoice_id, im.invoice_number as > invoice_number,im.invoice_date as created,im.status as status, > im.next_invoice as next_invoice, im.tax as tax,im.tax2 as > tax2,im.subtotal as subtotal,im.total as total,im.balance_due as > balance_due, im.customer_number as customer_number, > im.customer_name as customer_name FROM invoice_master as im LEFT JOIN > ( SELECT coalesce(sum(payment_applied), 0.00) as total_paid,invoice_id > as theiid FROM payments WHERE void='f' AND > created <= 1204243199) the_payments on im.invoice_id = > the_payments.theiid WHERE im.invoice_date between 1201478400 And > 1204243199 AND im.status != 'Forwarded' > GROUP BY im.invoice_id ORDER BY im.balance_due > DESC,im.invoice_date,im.total DESC,im.customer_name > > With or without the join I get the exact same result set. I don't even > see null results for the columns that are supposed to be pulled in > from the join. I have a habit of mixing SQLite and PostgreSQL syntax, > have I done it again? >
The above statement is basically unreadable. After it is formatted for human consumption it becomes: SELECT im.invoice_date as invoice_date, im.pay_by as due_date, im.invoice_id as invoice_id, im.invoice_number as invoice_number, im.invoice_date as created, im.status as status, im.next_invoice as next_invoice, im.tax as tax, im.tax2 as tax2, im.subtotal as subtotal, im.total as total, im.balance_due as balance_due, im.customer_number as customer_number, im.customer_name as customer_name FROM invoice_master as im LEFT JOIN ( SELECT coalesce(sum(payment_applied), 0.00) as total_paid, invoice_id as theiid FROM payments WHERE void='f' AND created <= 1204243199) the_payments on im.invoice_id = the_payments.theiid WHERE im.invoice_date between 1201478400 And 1204243199 AND im.status != 'Forwarded' GROUP BY im.invoice_id ORDER BY im.balance_due DESC, im.invoice_date, im.total DESC, im.customer_name Where you can easily see that the only columns you are selecting are the from the im table (invoice_master). The columns from the the_payments sub-query are not selected, so they are not displayed. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users