Apply condition on Outer join table, Not at WHERE Statement

[Guest]

Hello Sir


Apply condition on Outer join table, Not at WHERE Statement

I want to performing left outer join on selected data



SELECT I.ContactId, C.Name,

FROM (InquiryMaster I LEFT OUTER JOIN
ContactMaster C ON C.ContactId = I.ContactId and
C.deleteflage=false)

where inquiryMaster.temp="expression"




In above given quary I want select on data from Contactmaster that have
“deleteflage†,

Means I want to apply left outer join on selected data

I does not want to give such like condition in WHERE , because I am using
multiple table for outer join and applying condition on that table


Above given quary work in sql server, Please give any idea regarding it

 

[Gary Walter]

Apply condition on Outer join table, Not at WHERE Statement

I want to performing left outer join on selected data



SELECT I.ContactId, C.Name,

FROM (InquiryMaster I LEFT OUTER JOIN
ContactMaster C ON C.ContactId = I.ContactId and
C.deleteflage=false)

where inquiryMaster.temp="expression"




In above given quary I want select on data from Contactmaster that have
"deleteflage" ,

Means I want to apply left outer join on selected data

I does not want to give such like condition in WHERE , because I am using
multiple table for outer join and applying condition on that table


Above given quary work in sql server, Please give any idea regarding it

Hi Anup,

A typical solution in Access is to filter the "inner table"
in a prequery, then outer join "outside table" to prequery.

*Sometimes* you can use a subquery in the FROM
clause for the "inner table" but that subquery must not
have any further brackets within it.

for example, by using reserved field name "Name"
it would require brackets around it, and trying to
save the main query will fail.

If the field were named "ContName,"
the following would save okay in Access:

SELECT
I.ContactId,
C.ContName
FROM
InquiryMaster I
LEFT JOIN
[SELECT
Q.ContactId,
Q.ContName
FROM
ContactMaster Q
WHERE
Q.deleteflage=0]. As C
ON
C.ContactId = I.ContactId
WHERE
I.temp="expression";

Note that subquery surrounded by
brackets and ends in period when
saved by Access.

You can always create the SQL in code
and run it w/o changing "Name" -- you
just cannot *save* the query because Access
parser will choke on inner bracket(s) in
subquery used in FROM clause when it
tries to save it.

i.e.,

strSQL="SELECT I.ContactId, C.[Name] FROM " _
& "InquiryMaster I LEFT JOIN " _
& "(SELECT Q.ContactId, Q.Name FROM " _
& "ContactMaster Q WHERE Q.deleteflage=0) As C " _
& "ON C.ContactId = I.ContactId " _
& "WHERE I.temp="expression";"

Note how subquery only surrounded by "( ...)"

good luck,

gary