Several Queries combine into one.

I have several queries that I want to combine into one.  I have been reading and found a way to combine two of them, but what is the best way when you have several?  audit-query.docx
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You need to define what "combine" means.  You can JOIN them or UNION them depending on what you are trying to accomplish.  My guess is that a UNION is what you are moving towards but all of the result sets need to have the same column signature.  You are mostly there except for the AUDIT_ITM_PAT_ENC query.
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
I need to combine all the data from all the tables after a person tells the report which pat_id to look for.  
Once the pat_id is provided, I want a query that combines all of the fields in all of the tables where there is data relating to the pat_id.

I am just now learning SQL.  I have used crystal reports for years but that doesn't really help in this instance.

I am not sure what join or union will do or how to get those functions to combine all of the tables.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The fast way to answer your question is to say you need to use UNION ALL between the SELECT's:
SELECT [ACCOUNT_ID]
  ,[CHANGE_TIME]
  ,[ITEM]
  ,[LINE]
  ,[USER_ID]
  ,[PAT_ID]
  ,[NEW_VALUE_LINE]
  ,[NEW_EXTERNAL_VALUE]
  ,[NEW_INTERNAL_VALUE]
  ,[OLD_VALUE_LINE]
  ,[OLD_EXTERNAL_VALUE]
  ,[OLD_INTERNAL_VALUE]
FROM [Clarity].[dbo].[AUDIT_ITM_ACCOUNT]
where PAT_ID = 'z2977'
UNION ALL
SELECT [BED_ID]
  ,[CHANGE_TIME]
  ,[ITEM]
  ,[LINE]
  ,[USER_ID]
  ,[PAT_ID]
  ,[NEW_VALUE_LINE]
  ,[NEW_EXTERNAL_VALUE]
  ,[NEW_INTERNAL_VALUE]
  ,[OLD_VALUE_LINE]
  ,[OLD_EXTERNAL_VALUE]
  ,[OLD_INTERNAL_VALUE]
FROM [Clarity].[dbo].[AUDIT_ITM_BED]
where PAT_ID = 'z2977'
UNION ALL
SELECT [CLAIM_ID]
  ,[CHANGE_TIME]
  ,[ITEM]
  ,[LINE]
  ,[USER_ID]
  ,[PAT_ID]
  ,[NEW_VALUE_LINE]
  ,[NEW_EXTERNAL_VALUE]
  ,[NEW_INTERNAL_VALUE]
  ,[OLD_VALUE_LINE]
  ,[OLD_EXTERNAL_VALUE]
  ,[OLD_INTERNAL_VALUE]
FROM [Clarity].[dbo].[AUDIT_ITM_CLAIM]
where PAT_ID = 'z2977'
UNION ALL
SELECT [COVERAGE_ID]
  ,[CHANGE_TIME]
  ,[ITEM]
  ,[LINE]
  ,[USER_ID]
  ,[PAT_ID]
  ,[NEW_VALUE_LINE]
  ,[NEW_EXTERNAL_VALUE]
  ,[NEW_INTERNAL_VALUE]
  ,[OLD_VALUE_LINE]
  ,[OLD_EXTERNAL_VALUE]
  ,[OLD_INTERNAL_VALUE]
FROM [Clarity].[dbo].[AUDIT_ITM_COVERAGE]
where PAT_ID = 'z2977'
UNION ALL
SELECT [ACCOUNT_ID]
  ,[CHANGE_TIME]
  ,[ITEM]
  ,[LINE]
  ,[USER_ID]
  ,[PAT_ID]
  ,[NEW_VALUE_LINE]
  ,[NEW_EXTERNAL_VALUE]
  ,[NEW_INTERNAL_VALUE]
  ,[OLD_VALUE_LINE]
  ,[OLD_EXTERNAL_VALUE]
  ,[OLD_INTERNAL_VALUE]
FROM [Clarity].[dbo].[AUDIT_ITM_HSP_ACCT]
where PAT_ID = 'z2977'
UNION ALL
SELECT [PAT_ID]
  ,[PAT_ENC_DATE_REAL]
  ,[CHANGE_TIME]
  ,[ITEM]
  ,[LINE]
  ,[USER_ID]
  ,[PAT_ENC_CSN_ID]
  ,[CONTACT_DATE]
  ,[NEW_VALUE_LINE]
  ,[NEW_EXTERNAL_VALUE]
  ,[NEW_INTERNAL_VALUE]
  ,[OLD_VALUE_LINE]
  ,[OLD_EXTERNAL_VALUE]
  ,[OLD_INTERNAL_VALUE]
FROM [Clarity].[dbo].[AUDIT_ITM_PAT_ENC]
where PAT_ID = 'z2977'
UNION ALL
SELECT TOP 1000 [PAT_ID]
  ,[CHANGE_TIME]
  ,[ITEM]
  ,[LINE]
  ,[USER_ID]
  ,[NEW_VALUE_LINE]
  ,[NEW_EXTERNAL_VALUE]
  ,[NEW_INTERNAL_VALUE]
  ,[OLD_VALUE_LINE]
  ,[OLD_EXTERNAL_VALUE]
  ,[OLD_INTERNAL_VALUE]
FROM [Clarity].[dbo].[AUDIT_ITM_PATIENT]
where PAT_ID = 'z2977'

Open in new window

But you need to be aware that the number and data type of columns of each SELECT need match with the rest of SELECT'S and the column names will be set by the first SELECT.
Brian CroweDatabase AdministratorCommented:
Based on your response then I would suggest you just join each table on pat_id.  I don't know whether you need INNER or OUTER joins based on your request but you will basically need to join to each table and then just enumerate which columns you want in the SELECT clause.

SELECT ...
FROM Clarity.dbo.AUDIT_ITE_ACCOUNT AS Account
INNER JOIN Clarity.dbo.AUDIT_ITM_BED AS Bed
   ON Account.PAT_ID = Bed.PAT_ID
INNER JOIN Clarity.dbo.AUDIT_ITM_CLAIM AS Claim
   ON Account.PAT_ID = Claim.PAT_ID
INNER JOIN Clarity.dbo.AUDIT_ITM_COVERAGE AS Coverage
   ON Account.PAT_ID = Coverage.PAT_ID
...etc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
I solved it by creating seven subreports and linking all of them to the main report.  The main report just has the patient table in it, with pat_id.   I linked all subreports using pat_id.  It is working well.  I never could understand exactly how to write it in sequel.  I will try to do what you said, but it sounds so confusing.  Thank you for your assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.