Querying from multiple #temp table

Hi Guys,

I have a stored procedure that will create various temp tables using MSSQL server 2000.
Given below the following sample date stored on these temp tables:

#treftype table

reftypekey  leads   refdesc                   reftype
-----------------------------------------------------------
NULL           6         NULL                      NULL
2                 1         Outgoing Referral  NULL
3                 2         Physician               NULL
4                 3         Yellow Pages         M
6                 3         Advertising            R
-----------------------------------------------------------

#tpattotal table

reftypekey  patients    
-----------------------
2                1
3                1
4                3
6                3
-----------------------


#ttrans1 table

reftypekey  charges     payments          
------------------------------------------------
NULL           25.00        25.00
3                 400.00      NULL
4                 700.00      250.00
6                 1350.00    850.00
------------------------------------------------

If I issue a query to connect all these tables:

select treftype.reftypekey, treftype.refdesc, treftype.reftype, treftype.leads,
    reftype.expenditure, reftype.startdate, reftype.enddate, tpattotal.patients,
    ttrans1.charges, ttrans1.payments
from #treftype treftype
left join reftype reftype on reftype.referraltypekey = treftype.reftypekey
left join #tpattotal tpattotal on tpattotal.reftypekey = treftype.reftypekey
left join #ttrans1 ttrans1 on ttrans1.reftypekey = treftype.reftypekey

I got a result set below:


reftypekey  refdesc                  reftype   leads    patients  charges  payments
--------------------------------------------------------------------------------------------------
NULL          NULL                      NULL      6           NULL      NULL       NULL
2                Outgoing Referral  NULL      1           1            NULL       NULL
3                Physician               NULL      2           1            400.00    NULL
4                Yellow Pages         M           3           3            700.00     250.00
6                Advertising            R            3           3            1350.00   850.00
--------------------------------------------------------------------------------------------------

My problem is that charges = 25.00 and payment = 25.00 from #ttrans1 table (where reftypekey = NULL)
is not showing on the result?
I can't seem to figure out why I can't get this value.
I might have missed something on my query.
Kindly show me the correct query to get these values.

Result should be:

reftypekey  refdesc                  reftype   leads    patients  charges  payments
--------------------------------------------------------------------------------------------------
NULL          NULL                      NULL      6           NULL      25.00      25.00
2                Outgoing Referral  NULL      1           1            NULL       NULL
3                Physician               NULL      2           1            400.00    NULL
4                Yellow Pages         M           3           3            700.00     250.00
6                Advertising            R            3           3            1350.00   850.00
--------------------------------------------------------------------------------------------------

Please Help,

yorge


yorgeAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't use NULL values to join records. That's why the result it wasn't what you expected!

I didn't test this, so please try:

select treftype.reftypekey, treftype.refdesc, treftype.reftype, treftype.leads,
    reftype.expenditure, reftype.startdate, reftype.enddate, tpattotal.patients,
    ttrans1.charges, ttrans1.payments
from #treftype treftype
left join reftype reftype on reftype.referraltypekey = treftype.reftypekey
left join #tpattotal tpattotal on tpattotal.reftypekey = treftype.reftypekey
left join #ttrans1 ttrans1 on ISNULL(ttrans1.reftypekey, 0) = ISNULL(treftype.reftypekey, 0)

NOTE: ISNULL function would replace NULL values for the 2nd parameter value.
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi yorge!

Try changing "left join" with "left outer join".

So, instead of this :
   left join reftype reftype on reftype.referraltypekey = treftype.reftypekey
   left join #tpattotal tpattotal on tpattotal.reftypekey = treftype.reftypekey
   left join #ttrans1 ttrans1 on ttrans1.reftypekey = treftype.reftypekey

It should be like this :
   left outer join reftype reftype on reftype.referraltypekey = treftype.reftypekey
   left outer join #tpattotal tpattotal on tpattotal.reftypekey = treftype.reftypekey
   left outer join #ttrans1 ttrans1 on ttrans1.reftypekey = treftype.reftypekey

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
yorgeAuthor Commented:
hi,

I just tested your suggestion above...it still doesn't show the charges =  25.00 and payments = 25.00
Any other idea?

yorge

0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi yorge!

You could also use COALESCE.

select treftype.reftypekey, treftype.refdesc, treftype.reftype, treftype.leads,
    reftype.expenditure, reftype.startdate, reftype.enddate, tpattotal.patients,
    ttrans1.charges, ttrans1.payments
from #treftype treftype
left join reftype reftype on reftype.referraltypekey = treftype.reftypekey
left join #tpattotal tpattotal on tpattotal.reftypekey = treftype.reftypekey
left join #ttrans1 ttrans1 on COALESCE(ttrans1.reftypekey,0) = COALESCE(treftype.reftypekey,0)


Regards!
eNTRANCE2002 :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.