Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Conditions in Left Outer Join |
Wed, Feb 11 2015 5:21 PM | Permanent Link |
Adam H. | Hi,
I was wondering if the following is acceptable SQL: Select Customer, Count(Invoices) Invoices, Sum(Amount) Amount From Customers C left outer join Invoices I on (I.CustID = C.ID) and (not I.Paid) Group by Customer What I'm attempting to achieve is a list of every customer, but to show the number of invoices and amounts of invoices that are outstanding. If I move the "Not I.Paid" to a where clause instead, it will only show me a list of customer that have outstanding invoices. I want to see all customers instead, and those without invoices. I'm hoping by using it in the left outer join that it will only join records that are unpaid, but since it's a left outer join the statement won't affect the actual customer list. The other methods would be to use a union, or memory tables, but this looks like the simplest method - I'm just not sure if it's acceptable SQL or whether there are any traps? Cheers Adam. |
Thu, Feb 12 2015 5:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Select Customer, Count(Invoices) Invoices, Sum(Amount) Amount > From Customers C >left outer join Invoices I on (I.CustID = C.ID) and (not I.Paid) >Group by Customer This "'I'm hoping by" makes me think you haven't tried it, and , even without trying it (and I don't have anything suitable to try it on) something about it bothers me. I think you'd be better off trying something along the lines of Select Customer, Count(Invoices) Invoices, Sum(IF(not i.Paid,Amount,0)) Amount From Customers C left outer join Invoices I on (I.CustID = C.ID) Group by Customer Roy Lambert |
Sun, Feb 15 2015 5:41 PM | Permanent Link |
Adam H. | Good Morning Roy,
Thanks for the reply. Unfortunately the example I've given is a generic one (as people tend to understand invoices and customers). In my real world example, I need to show the other table even if there's no link at all. (So, in the example, I would still need a list of invoices as well - even if there are no customers attached). I have ended up trying the above and putting the condition in the left outer join. It seems to work. I wasn't aware whether there could be any complications however, as I've never put these types of conditions in the left outer join and wasn't sure whether it was accepted, or whether this was just a fluke and there are traps ahead. If no one has a firm understanding either way, then I'll see how I go with this, otherwise I might drop Tim a direct email to see his thoughts. Cheers Adam. |
Mon, Feb 16 2015 3:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
The join condition is essentially just a boolean test so I can cope with and understand that its the left/right inner/outer that I struggle with and generally end up experimenting. If it works use it. However, I like the idea of invoices without customers. Just what I need for a bit of creative fraud. I assume there's a good reason for this. If nnot I recommend a separate report highlighting the invoices without customers. Roy Lambert |
Mon, Feb 23 2015 12:25 AM | Permanent Link |
Adam H. | Hi Roy,
> The join condition is essentially just a boolean test so I can cope with and understand that its the left/right inner/outer that I struggle with and generally end up experimenting. If it works use it. Excellent - that's what I'm going with at this stage. > However, I like the idea of invoices without customers. Just what I need for a bit of creative fraud. I assume there's a good reason for this. If nnot I recommend a separate report highlighting the invoices without customers. LOL - it's called 'Creative Accounting Software', I'm expecting to make a small fortune. The real application has nothing to do with accounting. Since the stuff I do is relatively unique I find it's normally easier to convert what I'm trying to achieve to something common such as an accounting package for everyone else to understand. (Otherwise it creates more questions than I get answers ) Cheers Adam. |
This web page was last updated on Monday, May 6, 2024 at 12:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |