Queries and LEFT and RIGHT joins

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hi all,

I was hoping that somebody would be able to provide some assistance with a
query that has been bugging me. I have three tables tblCompetency,
tblCompetencyLog and tblCompLogLink:

tblCompetency
CompetencyID
CompetencyDesc

tblCompLog
CompLogID
CompLogDesc

tblCompLogLink
CompetencyID
CompLogID

(I am using the link table as competency's can occur in different
competency logs).

I am trying to develop a query that will allow me to list all records from
tblCompetency that have not been assigned to a particular competency log yet
(the log is chosen by a combo box on a form - but that is for later). I have
tried many variations (LEFT and RIGHT JOINS, even EXCEPT). I dont know if I
have been looking at it too long but at the moment I am stumped. Any help
anybody could provide would be fantastic,

Thanking you,
Rob
 
I understand your table relationship to be many-to-many with tblCompLogLink
to be the link table between tblCompetency and tblCompLog.

For your query to only show records from tblCompetency that have not been
assigned to a particular competency log yet, you only need to look at two
tables, tblCompetency and tblCompLogLink. You need to do a left join and
show all records from tblCompetency where there is a null value for
CompLogID.

Try pasting this into SQL view:

SELECT tblCompetency.CompetencyID, tblCompetency.CompetencyDesc,
tblCompLogLink.CompLogID
FROM tblCompetency LEFT JOIN tblCompLogLink ON tblCompetency.CompetencyID =
tblCompLogLink.CompetencyID
WHERE (((tblCompLogLink.CompLogID) Is Null));

John
 
Hi John,

Thanks for your response. It is a many-many relationship and I now have
that outer join query running. The real data I want to extract using this
query is all competency's that havent been assigned a particular competency
log. I had played around with the Is Null and LEFT JOIN before and had that
working but what I couldn't get working was any other type of parameter for
CompLogID. eg (there is some extra fields in this response :),

SELECT tblCompLogLink.CompetencyLogID, tblCompetencies.CompetencyNumber
FROM tblCompetencies LEFT JOIN tblCompLogLink ON
tblCompetencies.CompetencyID = tblCompLogLink.CompetencyID
WHERE (((tblCompLogLink.CompetencyLogID)<>2));

The way that I understand LEFT JOINS is that it will get unmatched records
from the left table and ignore unmatched records from the right. I feel like
I am missing something really simple! Not having played with databases for
a few years I am probably a bit scratchy. Any help would be appreciated,

Cheers
Rob
 
Back
Top