Further Narrowing Query with Inner Join (1 Viewer)

[Reese]

I have an Event Information table with a key field Event_ID, a Facilitators table with a key field Facilitator_ID, and a Facilitation table that records which facilitators went on which events with the following fields:

Facilitation_ID, Event_ID and Facilitator_ID.

There's a One to Many relationship between facilitators and events. Also multiple facilitators may go on one event. (E.g. Facilitator 1 and Facilitator 4 could go on the same event.)

It's easy to create a query to find all of the events a given facilitator went on, but I also want a query to find the events that a given facilitator went on solo, without any other facilitator present. (E.g. If Facilitator 1 went on Event A by themselves, then went on Event B with Facilitator 4, this query would only display Event A.)

Does anyone have any ideas on how to narrow those results down via a query in the Facilitation table? Thanks.

 

[CJ_London]

something like

select *
from eventtbl
where (select count(*) from eventtbl as T group by facilitator, event)=1

 

[Reese]

Thanks for the suggestion, but I'm not really following what you are getting at by using a *, or including a count function in the where statement. Maybe it would help if I posted a simple query with what I have:

SELECT [Event Information].Event_ID
FROM [Event Information] INNER JOIN FacilitationTable ON [Event Information].Event_ID = FacilitationTable.Event_ID
WHERE ( (FacilitationTable.Facilitator_ID=1)
                AND ([Event Information].Date_of_Event>#12/1/2015#)
                AND ([Event Information].Date_of_Event<#2/20/2016#)
               )
GROUP BY [Event Information].Event_ID;

This would display events that Facilitator 1 went on between the dates of 12/1/15 and 2/20/16. I added the dates to the Where statement so that I wasn't looking at hundreds of results. Plus, a 'between date X and Y' criteria will be added to this query once I know how to create it.

(Yes, there is a space in the Event table name. I was a novice when I initially created the database and if I were to have create it differently, I wouldn't have included the space.)

Thanks!

 

[CJ_London]

I suggested

select *
from eventtbl
where (select count(*) from eventtbl as T group by facilitator, event)=1

which to adapt to your code.

 SELECT E.Event_ID
FROM [Event Information] As E INNER JOIN ([COLOR=red]select Event_ID from FacilitationTable where (select Event_ID, count(*) from FacilitationTable as T group by event)=1[/COLOR][COLOR=black]) AS T1 ON E.Event_ID=T1.Event_ID[/COLOR]
 WHERE Date_of_Event>#12/1/2015# AND Date_of_Event<#2/20/2016#

 

[Reese]

I tried using the code you suggested and it resulted in the following error message:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

Could you also explain the technique you are suggesting I use? At this point you have gone far beyond my understand of SQL and I am simply copying/pasting without understanding what the code is doing. I had no idea that subqueries even existed, what the EXISTS function is, or how the labeling variables as E or T works.

In the meantime, I'll try to do some research on those things on my own. Thank you.

 

[Reese]

I think I got it! After reading more into subqueries, I started to get at least a basic understanding of what I'm dealing with and started playing around with code. This is what I created and it works:

SELECT [Event Information].Event_ID
FROM [Event Information] INNER JOIN FacilitationTable ON [Event Information].Event_ID = FacilitationTable.Event_ID
WHERE ( (FacilitationTable.Facilitator_ID=1)
                AND ([Event Information].Date_of_Event>#12/1/2015# And [Event Information].Date_of_Event<#2/20/2016#)
                AND [Event Information].Event_ID NOT IN
                         (SELECT [Event Information].Event_ID
FROM [Event Information] INNER JOIN FacilitationTable ON [Event Information].Event_ID = FacilitationTable.Event_ID
WHERE (FacilitationTable.Facilitator_ID<>1)
                        )
              )
GROUP BY [Event Information].Event_ID;

It may not be the most elegant piece of code, but it gets the job done. Thanks CJ, for at least pointing me in the direction of subqueries. If you think there's a way to streamline things, I'd be happy to hear your suggestions.