Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 3rd, 2008, 08:07 AM
Authorized User
 
Join Date: May 2008
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old June 3rd, 2008, 12:56 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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
Reply With Quote
  #3 (permalink)  
Old June 3rd, 2008, 12:57 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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.
Reply With Quote
  #4 (permalink)  
Old June 3rd, 2008, 03:12 PM
Authorized User
 
Join Date: May 2008
Location: , , .
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
Reply With Quote
  #5 (permalink)  
Old June 3rd, 2008, 04:10 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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
Reply With Quote
  #6 (permalink)  
Old June 3rd, 2008, 04:18 PM
Authorized User
 
Join Date: May 2008
Location: , , .
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
Reply With Quote
  #7 (permalink)  
Old June 4th, 2008, 04:36 AM
Authorized User
 
Join Date: May 2008
Location: , , .
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

Reply With Quote
  #8 (permalink)  
Old June 4th, 2008, 05:07 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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???
Reply With Quote
  #9 (permalink)  
Old June 4th, 2008, 08:15 PM
Authorized User
 
Join Date: May 2008
Location: , , .
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).
Reply With Quote
  #10 (permalink)  
Old June 5th, 2008, 07:00 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:55 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.