Multiple ON statements on a LEFT JOIN

  • Thread starter Thread starter JLuis Estrada
  • Start date Start date
J

JLuis Estrada

Can I have more than 1 ON statement on a LEFT JOIN?

Something like this:

LEFT JOIN Pacientes_Tel ON Pacientes.ClvPac = Pacientes_Tel.ClvPac AND
ON Pacientes_Tel.Def = True

is this possible?
 
No. If you need two coditions like this, you need to use AND or OR, depending
on what you want. If the intent is to only show rows from both tables where
BOTH conditions are true, use AND. If the intent is to only show rows from
both tables when EITHER condition is true, use OR.

LEFT JOIN Pacientes_Tel
ON Pacientes.ClvPac = Pacientes_Tel.ClvPac
AND Pacientes_Tel.Def = True

Good Luck!
 
I Tried that Before and didnt work. But I finally found an aswer:

I nest anothes query in the LEFT JOIN like this way

LEFT JOIN (SELECT Tel,ClvPac FROM Pacientes_Tel WHERE Def = CBool(-1)) AS
Telefonos ON Pacientes.ClvPac = Telefonos.ClvPac

and with that, I can filter the fields on that seconday table

JLuis
 
I didn't give the correct answer before in any event. I should have read your
post more carefully.

A JOIN of any kind specifies a condition for combining records from two
tables. It DOES NOT specify which records from the entire set should be
selected. The second clause that you had in the original post was not a valid
JOIN condition in any event. A Join condition will always specify a
relationship between two tables.

The way to write what you now have is:

Select <set of fields>
from Pacientes_Tel LEFT JOIN Pacientes
ON Pacientes_Tel.Clv_Pac = Pacientes.Clv_Pac
WHERE Pacientes_Tel.Def = True

There is no need for the subselect nested inside the JOIN.

Sorry about the original, only partially correct, response.
 
I've had a lot of trouble with multiple on statements. As JLuis Estrada, I
want to use two conditions to join two tables. Still the solution brang by
Chaim doesn't seem to fit my problem because my query (see below) contains a
combination of nested LEFT JOIN. So here's the query:

SELECT *
FROM
(
Jobs
LEFT JOIN
JobsLang
ON
Jobs.JobID=JobsLang.JobID
)
LEFT JOIN
SFWC_JobPostings_tb_Employers
ON
Jobs.EmployerID=Employers.EmployerID
AND ON
JobsLang.LanguageCode=Employers.LanguageCode
WHERE
SFWC_JobPostings_tb_JobsLanguageInfos.LanguageCode='en';

I used the LEFT JOIN ... ON ... AND ON syntax based on MS Access help (see
"INNER JOIN Operation") and it doesn't seem to work. Any help would be
greatly appreciated.
 
Hi,


The article you referred to has a couple of typos. The syntax is not AND
ON neither OR ON, but just AND or OR.

Even without that typo, your join is in error since is does not mention the
table Employeers except than in the ON clause, and it is ambiguous. You may
solve the ambiguity by writing a first query, which is probably about Jobs
and JobsLang, then, use a second query based on the first query you would
have just done (the query is used as a table) and the table Employeers.



Hoping it may help,
Vanderghast, Access MVP
 
Well... I corrected my query replacing AND ONs by ANDs only and as you told,
it still didn't work. Then I tried using one part of the query as a table.
Not sure I clearly understood what you meant there but here's what it gave:

SELECT *
FROM
(
Jobs
LEFT JOIN
JobsLang
ON
Jobs.JobID=JobsLang.JobID
)
LEFT JOIN
(
SELECT *
FROM
Employers
WHERE
Employers.LanguageCode=JobsLang.LanguageCode
)
ON
Employers.EmployerID=Jobs.EmployerID
WHERE
JobsLang.LanguageCode='en';

As you can see, I used a subquery as the second LEFT JOIN table. Still this
doesn't seem to work... Maybe M.Walsh can bring some more informations on
this solution. It would be greatly appreciated.

I made up another query, which is more simple, though less secure, but at
least working, using only SELECT ... FROM ... WHERE ... syntax. So this
problem went from code Red to Yellow.
 
Hi,



Make a first query with just


SELECT *
FROM Jobs LEFT JOIN JobsLang
ON Jobs.JobID=JobsLang.JobID




and save it under the name, say, q1. Note that since you have two fields
JobID, you will have to use something else than * in the SELECT clause.


Then


SELECT *
FROM q1 LEFT JOIN Employeers
ON q1.JobID = Employeers.JobID
WHERE q1.LanguageCode='en'




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top