Full Outer Join Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Full Outer Join Problem

Author

Topic

greyknght1
Starting Member

4 Posts

Posted - 2005-06-16 : 18:52:24

Ok firstly here is my T-SQL statement:

SELECT TOP 15 a.NIIN, a.SCM, a.Qty, isnull(b.Qty,0), (a.Qty - isnull(b.Qty,0)) FROM mot_view_Backorder_NIIN_Monthly_Rollup as a FULL OUTER JOIN mot_view_Backorder_NIIN_Monthly_Rollup as b ON a.NIIN = b.NIIN WHERE (a.ID=32 AND b.ID=31 AND a.NIIN='014937142') ORDER BY (a.Qty - isnull(b.Qty,0)) DESC

The problem that I am having is this: I am trying to pull information from two different months from the same table (view actually). ID is the month in this query. The problem lies in the fact that I can not pull any data for a record if it doesn't have a reference in the month 31. Meaning thus:
Month 32 NIIN = 014937142 and Qty = 30
Month 31 NIIN = 014937142 and Qty = 20
my difference field will return 10 but if
Month 32 NIIN = 014937142 and Qty = 30
and there is no reference to the NIIN in Month 31, nothing is returned. I need to pull a null value for the qty and then replace it with a 0 so my difference field will return 30.

Long story short, is there a way to do that?

Thanks!

Michael
greyknght1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-16 : 19:36:49

I am so confused. Why are you using a join if you only have one table?

Could you provide the DDL for your table as well as INSERT INTO statements for sample data and the expected result set using that sample data so that we can work on a solution for you on our own machines?

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-16 : 21:39:32

If I understand you correctly, I think you just need to do a LEFT JOIN (and I changed the ON and WHERE conditions slightly):

SELECT TOP 15 a.NIIN, a.SCM, a.Qty, isnull(b.Qty,0), (a.Qty - isnull(b.Qty,0))
FROM mot_view_Backorder_NIIN_Monthly_Rollup as a
LEFT JOIN mot_view_Backorder_NIIN_Monthly_Rollup as b
ON a.NIIN = b.NIIN AND a.ID = b.ID +1
WHERE a.ID = 32
ORDER BY (a.Qty - isnull(b.Qty,0)) DESC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-16 : 22:32:48

Please, no FULL OUTER JOINS! Arggh !

In fact, no joins at all are needed here .. you just need to put the data from each month into its own column and then do your math:

for example:


select ID, SUM(Month1), SUM(Month2), SUM(Month1) - SUM(Month2)
from
(
  select a.ID, 
    case when a.Month = 1 then Amount  else 0 end as Month1Amount,
    case when a.month=2 then Amount else 0 end as Month2Amount
  from
    YourTable
  where
    Month in (1,2)
) tmp
group by ID

- Jeff

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-16 : 22:59:51

Jeff's query works quite nicely, but it is slightly hardcoded. If that works for you, then that's fine. Otherwise, I think a self join will work more generally (you can remove the "WHERE a.ID = 32" from my query to make it more generalized).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-17 : 07:38:08

Huh? What is hard-coded? Can you be more specific? You are still allowed to use variables or parameters, you know!

Nothing is stopping you from saying:

case when a.Month = @Month then ...
and
case when a.month = @Month+1 then ...
and
where a.Month in (@Month, @Month+1)

While FULL OUTER JOINS are always bad, in this case even a LEFT OUTER JOIN is simply not needed and won't work as well if there is no data for the first month but there is for the second month (If that is possible) for any of the rows you are joining. Learning to "pivot" data like I have shown is one of th most valuable skills you can acquire in SQL.

Good rule of thumb -- you should NEVER join two transactional tables together (even the same one!). However, if you do find a need to join them, then you need to also be sure that the two transactional tables you join are fully GROUP'ed by common key columns first.

- Jeff

greyknght1
Starting Member

4 Posts

Posted - 2005-06-17 : 09:46:48

Thanks everyone for such a prompt response. I am going to try as many of your suggestions to see which works best. This has got to be the best SQL fourm out there.

greyknght1
Starting Member

4 Posts

Posted - 2005-06-17 : 10:07:26

The reason that the code is hard coded is because it really isn't. I am using ASP to dynamically generate a script to put increases and decreases in stocknumber qty's from the present month to the previous month. The ID's are actually dynamic along with the NIIN (stock number). Hope that clears a few things up.

greyknght1
Starting Member

4 Posts

Posted - 2005-06-17 : 10:31:16

Nosepicker,

Your solution nailed it! Thanks everyone for your input. I really appreciate it!

Thanks,

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources