[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Join Syntax Questions
From:       Dennis Cote <dennis.cote () gmail ! com>
Date:       2008-02-29 16:40:09
Message-ID: 47C83569.6040307 () gmail ! com
[Download RAW message or body]

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
[prev in list] [next in list] [prev in thread] [next in thread]