Becky Edwards
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
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.
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.
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'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.