Search code examples
sqlmultirow

Multirow return Query MSACCESS

I need to run a query that returns all events that has Electrophysiology Study But don't have %Ablation% So in this case I should receive only events 608 and 612. The table has two columns SS_EVENT_EP_ID and STUDYPROCEDURE

Screenshot of tables https://plus.google.com/photos/105880715521229058253/albums/6026235567691005409/6026235568072337762

Solution

  • I just saw your tables. Yes you are correct, you need a subquery.

    What you need is the EXISTS operator as well.

    SELECT EP.SS_Event_EP_ID, EP.StudyProcedure, Event_EP.EventDate 
FROM Event_EP INNER JOIN EP_Procedure As EP
ON Event_EP.SS_Event_EP_ID = EP.SS_Event_EP_ID 
WHERE EP.StudyProcedure = "Electrophysiology study" 
AND (Event_EP.EventDate Between #1/1/2004# And #12/31/2012#)
AND NOT EXISTS ( 
    Select SS_Event_EP_ID from EP_Procedure As EP_I
    Where EP_I.SS_Event_EP_ID = EP.SS_Event_EP_ID 
    And EP_I.StudyProcedure Like "%blation%"
)