|
Subject:
|
SQL statement that includes LEFT & RIGHT JOIN in
|
|
Posted By:
|
callagga
|
Post Date:
|
6/3/2008 8:07:20 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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/3/2008 12:56:09 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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/3/2008 12:57:33 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.
|
|
Reply By:
|
callagga
|
Reply Date:
|
6/3/2008 3:12:23 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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/3/2008 4:10:13 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
|
|
Reply By:
|
callagga
|
Reply Date:
|
6/3/2008 4:18:00 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
|
|
Reply By:
|
callagga
|
Reply Date:
|
6/4/2008 4:36:51 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)
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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/4/2008 5:07:18 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):
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:
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???
|
|
Reply By:
|
callagga
|
Reply Date:
|
6/4/2008 8:15:42 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).
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/5/2008 7:00:32 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):
<%
...
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.
|
|
Reply By:
|
callagga
|
Reply Date:
|
6/8/2008 1:58:11 AM
|
got an idea from someone I was chatting to.
Basically pre-create a table that has every date over the years you're interested in. Then for each date create a column for each sorting arrangement you might want, e.g. day number, week number, month number etc. Then join the expenses table with this on date. Pretty cool...
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/9/2008 1:19:14 AM
|
Yes, that's what I recommend for building event calendars.
http://www.ClearviewDesign.com/Newbie
See the "calendar of events" demo.
But I wasn't sure it was worth the effort here. If you can use it for other purposes, though, it clearly simplifies lots of queries.
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/9/2008 1:32:43 AM
|
You remember who it was who suggested the trick? Curious if he and I have "tallked".
|
|
Reply By:
|
callagga
|
Reply Date:
|
6/9/2008 2:30:02 AM
|
it's from Glenn, who has nothing to do with this forum, so I expect it would be extremely unlikely you know him...he does Crystal Reports
|
