Re: HOWTO: Use conditions in FROM clause in Access sintax.
- From: MGFoster <me@xxxxxxxxxxx>
- Date: Thu, 08 Sep 2005 20:25:43 GMT
-----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)
.
- Follow-Ups:
- Re: HOWTO: Use conditions in FROM clause in Access sintax.
- From: John Spencer (MVP)
- Re: HOWTO: Use conditions in FROM clause in Access sintax.
- References:
- Prev by Date: Re: show value if another field is yes
- Next by Date: Re: How do I import data from one table into another?
- Previous by thread: Re: HOWTO: Use conditions in FROM clause in Access sintax.
- Next by thread: Re: HOWTO: Use conditions in FROM clause in Access sintax.
- Index(es):
Relevant Pages
|
