Combination of left outer join and where clause

Dev Shed Forums Sponsor:

September 6th, 2006, 01:52 PM

Adrastea0413

Back after 2 years!

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Dec 2003

Location: Washington, DC Metro

Posts: 1,747

Time spent in forums: 1 Month 4 Days 23 h 43 m 8 sec
Reputation Power: 884

Combination of left outer join and where clause

I'm not sure if my subject line is good because it pretty much describes what *I* tried as the solution, which may not be the correct solution for this problem. If anyone can help me solve my problem, I'd be grateful.

I'll do my best to try and explain the problem I'm having. If I'm unclear, please let me know and I'll try to clarify.

To make matters easier, I am using fake table names and such in this example to make it easier for people to help without knowing exactly what everything is.

In this explanation, I'll use two tables, a department table and a finances table. The department table has a dept_id (PK) and a title. The finances table has a row_id (PK), a dept_id (FK to departments), a month and year to which the row's data is applicable, a revenue amount and an expenses amount.

Here is an example:

Code:

dept_id | title
--------+------------------
1111    | Human Resources
2222    | Accounting
3333    | Customer Service

row_id | dept_id | month | year | revenue | expenses
-------+---------+-------+------+---------+----------
1      | 1111    | 2     | 2006 | 10000   | 8000
2      | 1111    | 3     | 2006 | 15000   | 10000
3      | 2222    | 3     | 2006 | 12500   | 13000

My goal is to have a table that lists all the departments and the applicable revenue/expenses for a given month. For example, if I want to show month 2 and year 2006, the query would spit out:

Code:

dept_id | title            | month | year | revenue | expenses
--------+------------------+-------+------+---------+----------
1111    | Human Resources  | 2     | 2006 | 10000   | 8000
2222    | Accounting       |       |      |         |
3333    | Customer Service |       |      |         |

If I wanted to show month 4 and year 2006, the query would spit out:

Code:

dept_id | title            | month | year | revenue | expenses
--------+------------------+-------+------+---------+----------
1111    | Human Resources  |       |      |         |
2222    | Accounting       |       |      |         |
3333    | Customer Service |       |      |         |

The query that I wrote to handle this uses a left join and a where clause:

sql Code:

Original - sql Code
SELECT departments.dept_id, title, month, year, revenue, expenses FROM departments LEFT JOIN finances ON departments.dept_id = finances.dept_id WHERE (month = 2 OR month IS NULL) AND (year = 2006 OR year IS NULL) ORDER BY dept_id;

SELECT departments.dept_id, title, month, year, revenue, expenses FROM departments LEFT JOIN finances ON departments.dept_id = finances.dept_id WHERE (month = 2 OR month IS NULL) AND (year = 2006 OR year IS NULL) ORDER BY dept_id;

Using that query shows the exact result I posted with data for month 2 and year 2006 above. However, the problem arises when I want to show something like month 4 and year 2006.

That same query produces a result like:

Code:

dept_id | title            | month | year | revenue | expenses
--------+------------------+-------+------+---------+----------
2222    | Accounting       |       |      |         |
3333    | Customer Service |       |      |         |

Which, I'm assuming, is because there is no month = 4 or month is null in the finances table for Human Resources, so it doesn't show up.

I'm looking for is some help in constructing a better query. Am I at least on the right track? Any help would be greatly appreciated.

September 7th, 2006, 07:13 AM

Adrastea0413

Back after 2 years!

Join Date: Dec 2003

Location: Washington, DC Metro

Posts: 1,747

Time spent in forums: 1 Month 4 Days 23 h 43 m 8 sec
Reputation Power: 884

Ok, I got it working. I'm going to post the solution here so others can see if they have similar problems.

I ended up using a subquery to get the desired results.

It's Oracle-specific:

sql Code:

Original - sql Code
SELECT A.dept_id, title, month, year, revenue, expenses FROM departments A, (SELECT dept_id, month, year, revenue, expenses FROM finances WHERE month=4 AND year=2006) B WHERE A.dept_id = B.dept_id (+) ORDER BY A.dept_id

SELECT A.dept_id, title, month, year, revenue, expenses FROM departments A, (SELECT dept_id, month, year, revenue, expenses FROM finances WHERE month=4 AND year=2006) B WHERE A.dept_id = B.dept_id (+) ORDER BY A.dept_id

September 7th, 2006, 11:35 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

glad you solved it (although i would be leery of using that old deprecated (+) way of writing the outer join)

for next time, all you need to do is move the WHERE conditions to the ON clause --

Code:

select departments.dept_id
     , finances.title
     , finances.month
     , finances.year
     , finances.revenue
     , finances.expenses 
  from departments 
left 
  join finances 
    on departments.dept_id = finances.dept_id 
   and finances.month = 2 
   and finances.year = 2006 
order 
    by dept_id

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book