-3

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

golem74
  • 1
  • 3
  • What have you tried? This is a pretty basic query you are asking. Try reading for the `LIKE` operator. – parakmiakos Jun 18 '14 at 12:06
  • SELECT EP_Procedure.SS_Event_EP_ID, EP_Procedure_.StudyProcedure, Event_EP.EventDate FROM Event_EP INNER JOIN EP_Procedure ON Event_EP.SS_Event_EP_ID = EP_Procedure.SS_Event_EP_ID WHERE (((EP_Procedure.StudyProcedure)="Electrophysiology study") AND ((Event_EP.EventDate) Between #1/1/2004# And #12/31/2012#)); – golem74 Jun 18 '14 at 12:16
  • I tried like and not like %ablation but that does not work – golem74 Jun 18 '14 at 12:17
  • Every Event can have few procedures so if i'll run the query i'll receive all rows with Electrophysiology study butrecords where is Electrophysiology study but no %ablation% – golem74 Jun 18 '14 at 12:21
  • Maybe i need subquery. first i check for the "Electrophysiology study" and then for Not Like %ablation%? – golem74 Jun 18 '14 at 12:23

1 Answers1

0

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%"
)
parakmiakos
  • 2,994
  • 9
  • 29
  • 43
  • Thank you. I am getting syntax error "FROM Event_EP INNER JOIN EP_Procedure As EP" – golem74 Jun 18 '14 at 13:48
  • Are you sure this is in MS Access? If it is in another database, you should ommit the `As` – parakmiakos Jun 18 '14 at 14:01
  • MSASSESS 2010 B SELECT EP_Procedure_BIDMC.SS_Event_EP_ID, EP_Procedure_BIDMC.StudyProcedure, Event_EP.EventDate FROM Event_EP INNER JOIN EP_Procedure_BIDMC ON Event_EP.SS_Event_EP_ID = EP_Procedure_BIDMC.SS_Event_EP_ID WHERE (((EP_Procedure_BIDMC.StudyProcedure)="Electrophysiology Study") AND ((Event_EP.EventDate) Between #1/1/2014# And #6/1/2014#)); – golem74 Jun 18 '14 at 14:11
  • If EP_Procedure_BIDMC is the correct name, then just rename it in my query above. `FROM Event_EP INNER JOIN EP_Procedure_BIDMC As EP` – parakmiakos Jun 18 '14 at 14:15