Totally stupid Right Join Query question

  • Thread starter Thread starter Amy Blankenship
  • Start date Start date
A

Amy Blankenship

I have a query:

SELECT ResponseField.ResponseGroupID, ResponseField.ResponseFieldName,
PersonResourceResponse.PersonResourceResponseID,
PersonResourceResponse.ScenarioID, PersonResourceResponse.UserInput,
PersonResourceResponse.ResourceResponse, PersonResourceResponse.IsIrritated,
PersonResourceResponse.ResponseFieldID, PersonResourceResponse.PersonID
FROM PersonResourceResponse RIGHT JOIN ResponseField ON
PersonResourceResponse.ResponseFieldID = ResponseField.ResponseFieldID;

This works great...gives me all records in ResponseField and then the
matching values, if any, in PersonResourceResponse.

However, if I include the criterea WHERE PersonResourceResponse.PersonID = 3
or PersonResourceResponse Is NULL, I only get the (existing) records in
PersonResourceResponse where the PersonID is 3 or null. What I was
expecting is to get existing records, plus the records from ResponseField
that have no matches in the PersonResourceResponse.

I'm not exactly and SQL newbie, but I can't get my head around what's going
wrong here.

TIA;

Amy
 
Let me rephrase...The query is returning only rows where there has never
been any record with any PersonID, which in this particular case is one row.
What I want is essentially a blank page with all ResponseFieldRows and only
information in the PersonResourceResponse fields when the PersonID is 3.

Thanks;

Amy
 
With Access, sometimes, when you throw in a WHERE clause, Access treats it
as a INNER JOIN.

Create one query to do the WHERE, and a second to perform the RIGHT JOIN.
Base the latter on the former.
 
The problem is, I need this to be updatable and the ultimate aim is to omit
the Where clause and use a filter on the form's source. I don't think this
nested approach will work for this application. If so, I can't make the
mental leaps to the steps I'd have to take to make a filter work on an inner
query but not an outer query.

I'm wondering if a NOT In would work...

Thanks;

Amy
 
Amy Blankenship said:
The problem is, I need this to be updatable and the ultimate aim is to omit
the Where clause and use a filter on the form's source. I don't think this
nested approach will work for this application. If so, I can't make the
mental leaps to the steps I'd have to take to make a filter work on an inner
query but not an outer query.

I'm wondering if a NOT In would work...

Thanks;

Amy
Click to expand...

Amy,

If you manage to get the frustrated outer join to be updateable, post
back here and let me know.


Sincerely,

Chris O.
 
OK, what I did was this:

Query 1:
SELECT PersonResourceResponse.PersonResourceResponseID,
PersonResourceResponse.ScenarioID, PersonResourceResponse.PersonID,
PersonResourceResponse.UserInput, PersonResourceResponse.ResourceResponse,
PersonResourceResponse.IsIrritated, PersonResourceResponse.ResponseFieldID
FROM PersonResourceResponse
WHERE (((PersonResourceResponse.ScenarioID)=GetScenario()) AND
((PersonResourceResponse.PersonID)=GetPerson() Or
(PersonResourceResponse.PersonID) Is Null)) OR
(((PersonResourceResponse.ScenarioID) Is Null));

In other words, this filters the records down to what the where clause would
filter them to, but before the other table and the join comes into the
picture. I wound up needing two inner criteria, because when I moved from
one scenario to the next, the scenario filter failed, of course.

The functions simply returned the value of Public variables: theScenario and
thePerson.

Then:
SELECT ResponseField.ResponseGroupID, ResponseField.ResponseFieldName,
noAWPersonResponseFilter.PersonResourceResponseID,
noAWPersonResponseFilter.ScenarioID, noAWPersonResponseFilter.PersonID,
noAWPersonResponseFilter.UserInput,
noAWPersonResponseFilter.ResourceResponse,
noAWPersonResponseFilter.IsIrritated,
noAWPersonResponseFilter.ResponseFieldID
FROM ResponseField LEFT JOIN noAWPersonResponseFilter ON
ResponseField.ResponseFieldID = noAWPersonResponseFilter.ResponseFieldID;

This is an updateable query.

The scenario form OnCurrent sets the theScenario to Me.ScenarioID, unless
ScenarioID is NULL, in which case it sets theScenario on ScenarioID change.
The noAW in the query title tells me I can't run this query from outside
Access, because of the custom functions.

The form that has the above query as its rowsource is embedded in a form
that is based on PersonID. Unfortunately you can't depend on the
LinkMaster/LinkChild relationship to work, because you don't just want the
ones where PersonID is =, you want = or null. So I actually used
PersonResponseGroupID, which limits the return to only the types of
responses that person can give, for the Master/Child. In the OnCurrent for
the outer form, thePerson is set to Me.PersonID. In this case, no new
people would be added, so there was no need to trap for PersonID being null.
However, because the subform is embedded and apparently already loaded, I
did have to requery.

I also disabled all buttons until there was a valid ScenarioID on the page,
because launching the popup that depended on theScenario when theScenario
was wrong was pretty icky!

So you just got the benefit of about 8 hours of extreme frustration,
hair-pulling, teeth gnashing, and husband snapping-at.

Hope it helps :-);

Amy
 
The Where clause is applied after the Right Join has been made. In your
case, you must test for (PersonID = 3) before the Right Join is made and not
after it. With SQL-Server, I would use a subquery (named or unnamed) for
complex cases or - for simple cases - write:

.... RIGHT JOIN ResponseField ON (PersonResourceResponse.ResponseFieldID =
ResponseField.ResponseFieldID and PersonResourceResponse.PersonID = 3) ...


This is one of the difference between INNER JOIN and OUTER JOIN: with the
OUTER JOIN, the order of application of the criterias is relevant.
 
Back
Top