Outer join help please!

[yippie_ky_yay]

Outer join help please! - SOLVED

Hello,

here's the db structure on which I need to query (I didn't bother writing out the Person table):


tblGroups
---------
GroupID
GroupName


tblLevel
--------
LevelID
LevelDescription


tblRecords
----------
PersonID
GroupID
LevelID

By doing an outer join from Groups to Records, I can see when there are no records for a particular group but I need to go one more level. I need to show something when there are no records for a particular Level. Any ideas?

Thanks!
-Sean

 

[namliam]

simply "duplicate" the Groups-Records outer join for the join between Records and Level.

If there is no Record there will be no level, so a null on Leveldescribtion should do the trick.

Regards

 

[yippie_ky_yay]

Thank you for answering namliam,

Unfortunately I have not been able to get it to work (I may have misunderstood your instructions...?). If I add the other join it won't work because I'll get the "ambiguous outer join..." error. I also tried creating a new query (subquery) and adding the join to the old query, but that did not add anything.

Thanks!
-Sean

 

[yippie_ky_yay]

Solved!

Well, I'm happy to say that I was able to solve it.

SELECT t.GroupID, t.GroupName, t.LevelID, t.LevelDescription, r.PersonID
FROM (SELECT GroupID, GroupName, LevelID, LevelDescription
FROM tblLevel_LKP, tblGroups_LKP) AS t LEFT JOIN tblRecords AS r ON (t.GroupID=r.GroupID) AND (t.LevelID=r.LevelID)
ORDER BY t.GroupID, t.LevelID;

Hope it helps someone!

-Sean

 

[namliam]

To prevent the error and get it in 1 query your arrows in the design window must allway be pointing in 1 direction
tbl1 ==> tbl2 ==> tbl3 will work
tbl1 ==> tbl2 === tbl3 will fail
tbl1 <== tbl2 === tbl3 will work
tbl1 === tbl2 <== tbl3 will fail
tbl1 === tbl2 ==> tbl3 will work
tbl1 ==> tbl2 <== tbl3 will fail
tbl1 <== tbl2 <== tbl3 will work

The logic is if you do an outer join you are expecting "no values" on that end, no sence in joining "no values" is there?

Hope this helps... even now that its solved...

Greetz

 

[yippie_ky_yay]

Hey Mailman,

I did try it this way: "tbl1 ==> tbl2 ==> tbl3" which did not fail but it didn't give me the null values I was seeking (i.e I would get nulls in records for which there were no people assigned to a certain group - but records for when nobody has been assigned to a certain group were suppressed entirely).

I tried it again, this time using SQL Server which supports full outer joins and this made it a bit easier to create in the query builder.

Thanks again for your help!

-Sean

 

[namliam]

Yep, well you will need the "mother" (tbl1) record in Access. I dont know about SQL, but Oracle doesnt support that either. You would have to solve that one by using a join.

Glad you worked it out.

Regards