[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Querying from multiple #temp table

Posted on 2005-04-22
4
Medium Priority
?
226 Views
Last Modified: 2010-03-19
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


0
Comment
Question by:yorge
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13841176
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
 

Author Comment

by:yorge
ID: 13841292
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
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 320 total points
ID: 13842615
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
 
LVL 14

Assisted Solution

by:Renante Entera
Renante Entera earned 80 total points
ID: 13847788
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question