Re: HOWTO: Use conditions in FROM clause in Access sintax.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

How about?

SELECT Table1.Ref, Table1.RefTwo, Table2.Data
FROM Table1 LEFT JOIN Table2
ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt)
WHERE (Table2.Data = 'data3' OR Table2.Data Is Null)

MGFoster wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> You have to run my query in Access. I know it doesn't work in SQL
> Server. The microsoft.public.sqlserver.programming newsgroup showed me
> your OUTER JOIN example, when I asked them why my workable Access query
> didn't run in SQL Server. Therefore, run the example I gave on Access,
> only.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQyCeI4echKqOuFEgEQIVKACgsQpOMMSzTNRlU48q0D+4vVMI/AkAn2U1
> bzjaMbUNbGHeuodA/Vr14wMB
> =KDf0
> -----END PGP SIGNATURE-----
>
> NKN wrote:
> > Hi,
> >
> > Thanks for your quick answer but I am afraid that the select you have wrote
> > doesn't give the same results as the one that I wrote with the condition of
> > Table2.Data='data3' in FROM clause. If you execute them in SQL Server I
> > mean.
> >
> > If you execute your select with the condition in the WHERE clause you only
> > get one row, that woth Table2.Data = 'data3' . But it is a LEFT OUTER JOIN
> > and that means that I need all rows in Table1 and only matching one in
> > Table2, so I would get 7 rows. 6 of them with null in Table2.Data field and
> > another one with data3.
> >
> > This is the result I get in SQL Server, but I can't in Access because of
> > some kind of syntactic problem.
> >
> > Any ideas?
> >
> > Thank you again,
> >
> > Nekane.
> > "MGFoster" <me@xxxxxxxxxxx> escribió en el mensaje
> > news:dgITe.7487$Wd7.5466@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >
> >>NKN wrote:
> >>
> >>>Hello all,
> >>>
> >>>I am triying to use condition wtih some fields in the FROM clause of an
> >>>Access query. But I get an error saying that "the conbination expresion
> >
> > is
> >
> >>>not valid".
> >>>
> >>>I know that this kind of sintax is valid in SQL Server but I ignore if
> >
> > there
> >
> >>>is another valid way to write this condition in the FROM clause. Does
> >>>anybody know how to do it?
> >>>
> >>>I am using 2 simple tables donde in Access. Table1 and Table2.
> >>>
> >>>Table1 has 2 fields. Table2 has 3 fields. The 2 fields in Table1 are
> >
> > related
> >
> >>>to the first 2 fields in Table2. I need to make a LEFT JOIN to get all
> >>>records in Table1 but only the record that matches the condition in
> >
> > Table2.
> >
> >>>This is the query I'm triying to execute.
> >>>
> >>>SELECT Table1.Ref, Table1.RefTwo, Table2.Data
> >>>FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND
> >>>(Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3');
> >>>
> >>>This are the data I am using.
> >>>
> >>> Table1 Ref RefTwo
> >>> ref1 ref11
> >>> ref2 ref21
> >>> ref3 ref31
> >>> ref4 ref41
> >>> ref5 ref51
> >>> ref6 ref61
> >>> ref7 ref71
> >>> ref8 ref81
> >>> ref9 ref91
> >>>
> >>>
> >>> Table2 Ref RefExt RefTwo Data
> >>> 1 ref2 ref21 data1
> >>> 2 ref2 ref21 data2
> >>> 3 ref3 ref31 data3
> >>> 4 ref4 ref41 data4
> >>> 5 ref5 ref51 data5
> >>> 6 ref5 ref51 data6
> >>> 7 ref5 ref51 data7
> >>>
> >>>
> >>>I need to get this result: Only teh record in Table2 that matches the
> >>>condition. The rest are not in the result because are NULL.
> >>> Result RefExt RefTwo Data
> >>> ref1 ref21
> >>> ref2 ref21
> >>> ref3 ref31 data3
> >>> ref4 ref41
> >>> ref5 ref51
> >>> ref6 ref61
> >>> ref7 ref71
> >>>
> >>>
> >>>
> >>>This is the result I get in SQL Server if I execute that query. Is it
> >>>possible to do it in Access with the conditions in FROM?
> >>
> >>Like this:
> >>
> >>SELECT Table1.Ref, Table1.RefTwo, Table2.Data
> >>FROM Table1 LEFT JOIN Table2
> >> ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt)
> >>WHERE (Table2.Data = 'data3')
> >>
> >>--
> >>MGFoster:::mgf00 <at> earthlink <decimal-point> net
> >>Oakland, CA (USA)
> >
> >
> >
.

Relevant Pages