Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 3rd, 2008, 08:07 AM
Authorized User
 
Join Date: May 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL statement that includes LEFT & RIGHT JOIN in

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 June 3rd, 2008, 12:56 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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 June 3rd, 2008, 12:57 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old June 3rd, 2008, 03:12 PM
Authorized User
 
Join Date: May 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 June 3rd, 2008, 04:10 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
 
Old June 3rd, 2008, 04:18 PM
Authorized User
 
Join Date: May 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 4th, 2008, 04:36 AM
Authorized User
 
Join Date: May 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 June 4th, 2008, 05:07 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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???
 
Old June 4th, 2008, 08:15 PM
Authorized User
 
Join Date: May 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 June 5th, 2008, 07:00 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
left outer join keyvanjan Classic ASP Basics 1 April 15th, 2006 05:37 AM
left outer join keyvanjan Classic ASP Professional 0 February 5th, 2006 11:54 AM
left join msrnivas Classic ASP Databases 2 October 15th, 2004 07:37 AM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
sql & join tables & find a field in multiple table trangd Beginning PHP 2 January 29th, 2004 07:18 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.