Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to merge two select queries

Author  Topic 

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-08-13 : 03:23:04
[code]
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 1 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)

[/code]


[code]
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND NOT EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)


[/code]


hi both the queries are identical bit only change is one is having exists as codition and other is having not exists as condition now i want to merge both for looking for one select query.can you help me out in doing so

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-08-13 : 03:23:54
if displaying same columns with same datatype use union
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-08-13 : 03:27:40
i have used union all operator now i wnt to mahe it for a sigle select query
Go to Top of Page

dwill
Starting Member

4 Posts
Posted - 2009-08-13 : 04:16:27
Hang on - am i missing something here but the first query gets "those that exist", and second gets "those that don't" - and you want to get both lots together in one query?

Just remove the second query and then remove the "and exists" clause from the first - i.e. return everything!
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-08-13 : 04:51:47
can you give me the query dwill
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-08-13 : 05:07:28
IS IT CORRECT

SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 1 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
AND NOT EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-08-13 : 05:10:59
quote:
Originally posted by rajasekhar857

IS IT CORRECT

SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 1 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
AND NOT EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-08-13 : 05:12:03
if i want to include then how can i able to write those
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-08-13 : 05:25:21
hi in the above queries first query is having 1 AS IS_ACTIVE
second query is having 0 AS IS_ACTIVE .
in such cases how can i merge it into a single query
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts
Posted - 2009-08-13 : 05:58:28
maybe do an order by IS_ACTIVE in bklr's corrected query

--------------------
Rock n Roll with SQL
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-08-13 : 06:09:34
bklr's corrected query still sets active = 1 for everyone.

You could try this (replacing the AND EXISTS portion for a left join. Then a CASE statement to determine if they are active or not based on the join conditions of the derived table.

SELECT
	ET.EMR_TRANSACTION_ID
	, ET.TASK_ID
	, 'ENCOUNTER' AS TASK_NAME
	, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID
	, ET.ID_VALUE
	, TO_CHAR(ET.FLOW_ID) AS FLOW_ID
	, TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE
	, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME
	, TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE
	, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID
	, EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS
	, EAL.APPOINTMENT_STATUS
	, EPM.PATIENT_FIRSTNAME
	, EPM.PATIENT_LASTNAME
	, EPM.PATIENT_DOB
	, EPM.PATIENT_HOMEPHONE
	, EPM.PATIENT_ID
	, EPM.PATIENT_TYPE
	, EPM.PATIENT_GENDER
	, EPM.RACE_ETHNICITY_ID
	, EPM.MARITAL_STATUS
	, '' AS NAME
	, ET.USER_LOGIN AS CONSULTANT_ID
	, ECD.CONSULTANT_NAME
	, ET.ROLE_NAME
	, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID
	, EPM.REGISTER_ID AS REGISTER_ID
	, NVL(IS_EXPRESS,0) AS IS_EXPRESS
	, 0 AS LAB_REQUEST_ID
	, 0 AS SPECIMEN_ID

	, CASE
		WHEN activeCheck.PATIENT_ID IS NULL THEN 0
		ELSE 1
	END AS IS_ACTIVE 
FROM
	EMRAppointmentDetailsHistory EA
	LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID 
	LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
	LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
	LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID

	LEFT JOIN (
		SELECT
			PATIENT_ID AS PATIENT_ID
			, ID_VALUE AS ID_VALUE
		FROM
			EMRTRANSACTIONS
        WHERE
			TASK_ID=108
		)
		activeCheck ON
			activeCheck.PATIENT_ID = ET.PATIENT_ID
			AND activeCheck.ID_VALUE = ET.ID_VALUE
WHERE
	ET.TASK_ID = 9
	AND EA.APPOINTMENT_STATUS_ID = 2


Oh -- what's this syntax in your select list by the way.

, EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS



I've not seen that before and don't think its TSQL


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts
Posted - 2009-08-13 : 06:13:50
good catch Charlie, overlooked both

--------------------
Rock n Roll with SQL
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-08-13 : 07:21:13
yes its working fine thanks for your suggestions
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site