Joining a Table to itself... with a twist

[axl169]

Hello,
I am working on a union with two views created from a join. Due to a bug in out db, we are unable to do full outer joins. Anyway, I have one table with 4 fields I am interested in. Sales_Rep, Month, Year, and amount. I am trying to created a view that will pull back selected year information and last year information in a column next to it.

For instance, sales rep 1, sold to Nike, Puma, and Adidas in 2007 for $100, and in 2008, they sold to Nike, Puma, and Reebok for $200. Is there anyway to create SQL that will have my output look something like this if I search for Sales Rep 1 for 2008?

Rep 1 | Nike | $100 |$200
Rep 1 | Puma | $100 |$200
Rep 1 | Adidas | $100 |
Rep 1 | Reebok | |$200


Thank you all in advance.

 

[r937]

yes, there is

please show the table layout

r937.com | rudy.ca

 

[LKBrwnDBA]

...Due to a bug in out db, we are unable to do full outer joins.

Quick! get rid of that DB.

On the other hand, what have you got coded up to now?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[axl169]

We are using Oracle, forget what version, but it does not allow full outer joins on views. I think I found a work around. Thank you for everyone's help regardless.