SQL statement that includes LEFT & RIGHT JOIN in

callagga · June 3rd, 2008, 08:07 AM

Hi,

How an I write an SQL statement with a GROUP BY that will both (a) include the NULL value from the left hand table, but also (b) include ALL columns from the right hand table. It's like I need a LEFT JOIN and a RIGHT JOIN in the query at the same time.

Here's an example of the 2 tables I have and the result I'm after. As you can see I want the NULL's from Expenses Table summed, as well as include all categories from the right hand table (i.e. even if there are no expenses against them)

Table = Expenses
Amount Category
$10 1
$20 2
$30 1
$40 NULL {i.e. not yet categorised}

Table = Categories
ID Title
1 Food
2 Entertainment
3 Travel
4 Personal

REQUIRED RESULT
Category Total
Food 40
Entertainment 20
Travel 0
Personal 0
NULL 40

Thanks

Old Pedant · June 3rd, 2008, 12:56 PM

Easiest thing is to this with a UNION, I think.

SELECT C.Title, SUM(ISNULL(E.Amount,0.0)) AS Total
FROM categories AS C LEFT JOIN expenses AS E
ON C.ID = E.Category
    UNION
SELECT 'not categorized yet', SUM(E.Amount)
FROM expenses WHERE category IS NULL
ORDER BY C.Title

Nothing more than that. That's for SQL Server. If you want it for Access:

SELECT C.Title, SUM(IIF(ISNULL(E.Amount),0.0,E.Amount)) AS Total
FROM categories AS C LEFT JOIN expenses AS E
ON C.ID = E.Category
    UNION
SELECT 'not categorized yet', SUM(E.Amount)
FROM expenses WHERE category IS NULL
ORDER BY C.Title

If you want the NULL to appear at the end of the list, just add a dummy field and change the order by:

SELECT 1 AS ordering, C.Title, SUM(ISNULL(E.Amount,0.0)) AS Total
FROM categories AS C LEFT JOIN expenses AS E
ON C.ID = E.Category
    UNION
SELECT 2, 'not categorized yet', SUM(E.Amount)
FROM expenses WHERE category IS NULL
ORDER BY ordering, C.Title

Old Pedant · June 3rd, 2008, 12:57 PM

Sorry...should just be
SUM(Amount)
for the second SELECT in the UNION. I didn't bother to alias the table to E since there is only one table used.

callagga · June 3rd, 2008, 03:12 PM

Thanks heaps.

One minor question - Is it possible to have the output such that instead of the word "NULL" appearing the text "Non Categorised Expenses" appears instead?

Thanks again

Old Pedant · June 3rd, 2008, 04:10 PM

Ummm....you didn't read my code, did you???

I *ALREADY* did that for you!!!

SELECT 1 AS ordering, C.Title, SUM(ISNULL(E.Amount,0.0)) AS Total
FROM categories AS C LEFT JOIN expenses AS E
ON C.ID = E.Category
UNION
SELECT 2, 'not categorized yet', SUM(E.Amount)
FROM expenses WHERE category IS NULL
ORDER BY ordering, C.Title

callagga · June 3rd, 2008, 04:18 PM

arrr, got it sorry. Well I guess read it quickly and shooting off a quick reply before heading out != read & understood & tried the code. Thanks again. Really appreciated

callagga · June 4th, 2008, 04:36 AM

Thanks again,

There's one additional twist this time I'm pretty sure is NOT possible in SQL, however given what you've been able to demonstrate re SQL I'd love to ask.

If I actually wanted to have the report broken down into periods (i.e. each X days) would this be possible? That is per the below. That is each expense has a date, and I want to get a summary per Period (e.g. X days wide)

Code:

REQUIRED OUTPUT
===============
Category      13/4/08-15/4/08     16/4/08-18/4/08      29/4/08-20/4/08      etc
-------       ---------------     ---------------      ---------------      ----
Entertainment 20.00               10                   0
Food          40.00               5                    20
Personal      0.00                0                    0
Travel        0.00                0                    10
NULL          40.00               80                   100

INPUT
=====
* Category table => per previous posts
* Expense table => per previous BUT with a new TransactionDate column.

Cheers
Greg

Old Pedant · June 4th, 2008, 05:07 PM

Well, you *could* do this, but it would amost surely be easier to do it what every reporting mechanism it is that you are using.

But just to show an example (not using LEFT JOIN or UNION, just for simplicity, using name "tdate" for brevity):

Code:

SELECT 1 AS ordering, 
         C.Title, 
         SUM( CASE WHEN tdate BETWEEN '13/4/2008' AND '15/4/2008' THEN Amount ELSE 0.0 END ) AS Total1,
         SUM( CASE WHEN tdate BETWEEN '16/4/2008' AND '18/4/2008' THEN Amount ELSE 0.0 END ) AS Total2,
         SUM( CASE WHEN tdate BETWEEN '19/4/2008' AND '20/4/2008' THEN Amount ELSE 0.0 END ) AS Total3,
         ...
FROM expenses AS E INNER JOIN categories AS C
ON C.ID = E.Category
GROUP BY ordering, C.title

If you do it via reporting, then it's pretty easy and much more flexible:

Code:

SELECT 1 AS ordering, 
         C.Title, 
         DATEDIFF( day, '13/4/2008', tdate ) / 2 AS periodNumber,
         SUM( ISNULL(E.Amount,0.0) ) AS Total1
FROM expenses AS E LEFT JOIN categories AS C
ON C.ID = E.Category
GROUP BY ordering, C.title, DATEDIFF( day, '13/4/2008', tdate ) / 2
ORDER BY ordering, C.title, periodNumber

Hmmm???

callagga · June 4th, 2008, 08:15 PM

thanks for the "DATEDIFF" idea! - I'll look into using it & trying it out.

I guess if such a SQL statement works this would still be better than my Ruby code issuing a SQL call for each specific period (i.e. as it works out sums for each period).

Old Pedant · June 5th, 2008, 07:00 PM

Haven't used Ruby, but oh yeah oh yeah...has to be *MUCH* faster!

In ASP/VBScript code, you could turn that SQL into a "report" pretty easily, thus (again, simplifying the query for brevity):

Code:

<%
...
SQL = "SELECT C.Title, " _
    &       " DATEDIFF( day, '13/4/2008', tdate ) / 2 AS periodNumber, " _
    &       " SUM( ISNULL(E.Amount,0.0) ) AS Total " _
    & " FROM expenses AS E LEFT JOIN categories AS C " _
    & " ON C.ID = E.Category " _
    & " WHERE tdate BETWEEN '13/4/2008' AND '22/4/2008' " _
    & " GROUP BY C.title, DATEDIFF( day, '13/4/2008', tdate ) / 2 " _
    & " ORDER BY C.title, periodNumber "

Set RS = connObj.Execute( SQL )
%>
<TABLE Border=1 Cellpadding=3>
<TR>
    <TH>Title</TH>
<% For Period = #2008-4-13# To #2008-4-22# Step 2 %>
    <TH><%=Period%><br/><%=(Period+1)%></TH>
<% Next %>
</TR>
<%
priorTitle = ""
Do Until RS.EOF
   curTitle = RS("Title")
   If curTitle <> priorTitle Then
       If priorTitle <> "" Then Response.Write "</TR>" & vbNewLine
%>
<TR>
    <TD><%=curTitle%></TD>
<%
       priorTitle = curTitle
   End If
%>
    <TD><%=FormatCurrency(RS("Total")%></TD>
<%
    RS.MoveNext
Loop
RS.Close
%>
</TR>
</TABLE>

That code has some flaws: As written, if there is not data for some period for a given title, it will put the next total into that period, etc. Can easily fix that in the code, but since there are other (purposeful) omissions (such as the UNION) I left it as is, just so the code is simple and understandable.