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
