Link to home
Start Free TrialLog in
Avatar of Becky Edwards
Becky EdwardsFlag for United States of America

asked on

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
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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.
Avatar of Becky Edwards

ASKER

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.