Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


TSQL Scenario

Posted on 2005-04-14
Medium Priority
Last Modified: 2010-04-17
You have a table named Providers, which has two fields, ProviderCode and ProviderName.  You have a second table called Accounting with two fields AccountProviderCode and AccountPatientCode.  Write a single TSQL statement to generate a third table called Merged, combining the ProviderName from the Providers table with all the records and fields from the Accounting table.
Question by:brucegust

Expert Comment

ID: 13782474
insert into Merged
            A.ProviderName as ProviderName , B.*      
            Providers A
      cross join
            Accounting B
LVL 22

Accepted Solution

JesterToo earned 2000 total points
ID: 13795054
Assuming your "Merged" table exists and has columns in this order "ProviderCode, ProviderName, PatientCode", then I suspect the following sql query is what you want instead of a cross join... (if the column order doesn't match, just rearrange the columns in the select statement or add a clause onto the end of the "insert..." statement like this:  "(ProviderCode, ProviderName, PatientCode)" using the actual column names in Merged.)

   insert into Merged
   select B.AccountProviderCode, coalesce(A.ProviderName,'Missing!'), B.AccountPatientCode
     from Providers A
   right outer join Accounting B on B.AccountProviderCode = A.ProviderCode

Since you didn't state whether there was a FK relationship between the Providers and Accounting tables it is possible to have rows in the Accounting table with Providers that don't exist in the Providers table.  This query will show all rows from the Accounting table, match to the Providers table when it can, and indicate when it can't.

Technically, since the column names in Providers and Accounting aren't ambiguous (they're all unique), the aliases (A and B) aren't required.  However, it is good practice to specify them when multiple tables are involved if only for clarity.


Expert Comment

ID: 13827295
if, on the other hand, you know that there is exactly one AccountProviderCode for each ProviderCode, then the simplest query would be this:

 SELECT ProviderName, AccountProviderCode, AccountPatientCode
 FROM Providers, Accounting WHERE ProviderCode = AccountProviderCode

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Loops Section Overview

580 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