Intersect query with no duplicates

Question

I have not used sql server in a large complex scale in years, and Looking for help on how to proper sintax intersect type query to joing these two data sets, and not create duplicate names. Some patients will have both an order and a clinical event entry and some will only have a clinical event.

Data Set 1

 SELECT 
  distinct
  ea.alias as FIN,
    per.NAME_Last + ', ' + per.NAME_FIRST + ' ' + Isnull(per.NAME_MIDDLE, '')     as PatientName,
oa.action_dt_tm as CirOrder,
od.ORIG_ORDER_DT_TM  as DischOrder,
e.disch_dt_tm  as ActualDisch,
prs.NAME_FULL_FORMATTED as OrderedBy,
from pathway py
join encounter e on e.CERNER_ENCOUNTER_ID = py.encntr_id
join encntr_alias ea on ea.CERNER_ENCNTR_ID = e.CERNER_ENCOUNTER_ID and ea.ENCNTR_ALIAS_TYPE_WCD = 1049
join person per on per.CERNER_PERSON_ID = e.cerner_PERSON_ID
join orders o on o.CERNER_ENCNTR_ID= e.CERNER_ENCOUNTER_ID and o.CATALOG_wCD = '82111'  -- communication order
          and o.pathway_catalog_id = '43809296'   ---Circumcision Order
join order_action oa on oa.[CERNER_ORDER_ID] = o.CERNER_ORDER_ID and oa.ACTION_TYPE_WCD = '2494'--ordered
join orders od on od.CERNER_ENCNTR_ID= e.CERNER_ENCOUNTER_ID and od.CATALOG_WCD = '203520'   --- Discharge Patient
join prsnl prs on prs.CERNER_PERSON_ID = oa.order_provider_id
where py.pathway_catalog_id =  '43809296' and        ---Circumcision Order
oa.action_dt_tm >  '2016-01-01 00:00:00'
and oa.ACTION_DT_TM < '2016-01-19 23:59:59'
--use the report prompts as parameters for the action_dt_tm

Data Set 2

SELECT 
distinct e.[CERNER_ENCOUNTER_ID],
ea.alias as FIN,
per.NAME_Last + ', ' + per.NAME_FIRST + ' ' + Isnull(per.NAME_MIDDLE, '') as PatientName,
ce.EVENT_END_DT_TM as CircTime,
od.ORIG_ORDER_DT_TM  as DischOrder,
e.disch_dt_tm  as ActualDisch,
'' OrderedBy,    -- should be blank for this set
cv.DISPLAY
from encounter e 
join clinical_event ce on e.CERNER_ENCOUNTER_ID = ce.CERNER_ENCNTR_ID
join encntr_alias ea on ea.CERNER_ENCNTR_ID = e.CERNER_ENCOUNTER_ID and ea.ENCNTR_ALIAS_TYPE_WCD = 1049
join person per on per.CERNER_PERSON_ID = e.cerner_PERSON_ID
join orders od on od.CERNER_ENCNTR_ID= e.CERNER_ENCOUNTER_ID and od.CATALOG_WCD = '203520'   --- Discharge Patient
left outer join ENCNTR_LOC_HIST elh on elh.CERNER_ENCNTR_ID = e.CERNER_ENCOUNTER_ID 
left outer join CODE_VALUE cv on cv.CODE_VALUE_WK = elh.LOC_NURSE_UNIT_WCD
where ce.event_wcd = '201148'      ---Newborn Circumcision
and ce.[RESULT_VAL] = 'Newborn Circumcision'
and ce.EVENT_END_DT_TM > '2016-01-01 00:00:00'
and ce.event_end_dt_tm < '2016-01-19 23:59:59’
and ce.RESULT_STATUS_WCD = '25'
and elh.ACTIVE_STATUS_DT_TM < ce.event_end_dt_tm  -- Circ  time between the location's active time and end time.
and elh.END_EFFECTIVE_DT_TM > ce.[EVENT_END_DT_TM]
--use the report prompts as parameters for the ce.[EVENT_END_DT_TM]

Your title says intersect but you've used both the intersect and union-all tags. Usage for both `intersect` and `union all` are in the SQL books online (https://msdn.microsoft.com/en-us/library/ff848745.aspx). Or are you asking for something else specifically? — Brian Pressler, Mar 09 '16 at 22:50
I believe I should be more on the lines of an intersect query to get the output I need. Just not sure how to create the syntax for the queries above. — Tony77, Mar 09 '16 at 22:51
What is the issue? What did you try? Or do you just don't want do it yourself? — Philipp, Mar 09 '16 at 23:00
no errors , since not sure how to structure the intersect command and the requirements. — Tony77, Mar 09 '16 at 23:19

Brian Pressler · Answer 1 · 2016-03-10T00:15:09.630

The structure of an intersect query is as simple as:

select statement 1
intersect
select statement 2
intersect
select statement 3
...

This will return all columns that are in both select statements. The columns returned in the select statements must be of the same quantity and type (or at least be convertible to common type).

You can also do an intersect type of query just using inner joins to filter out records in the one query that are not in the other. So for a simple example let's say you have two tables of colors.

Select distinct ColorTable1.Color
from ColorTable1
join ColorTable2
on ColorTable1.Color = ColorTable2.Color

This will return all the distinct colors in ColorTable1 that are also in ColorTable2. Using joins to filter could help your query perform better, but it does take more thought.

Also see: Set Operators (Transact-SQL)

Thanks Brian, Can you please modify the one above in the proper way it should be structured? — Tony77, Mar 10 '16 at 12:54

Intersect query with no duplicates

1 Answers1