HOWTO: Use conditions in FROM clause in Access sintax.

N

NKN

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?

TIA,

Nekane.
 
M

MGFoster

NKN said:
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?
Click to expand...

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')
 
N

NKN

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.
 
V

Vincent Johns

NKN said:
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.
Click to expand...

There's more than just a syntactic problem (see below).

[...]
I don't see why you get that result in SQL Server, because to me it just
looks wrong. The way you have your query set up, there is NO value in
Table2 that matches either of the fields in the first record of Table1,
and there are no fields in any other records in either table which match
either of those values. So my guess is that one of your files got
corrupted. You might try running the query again on SQL Server.

Attempting to develop a query that would come close to what you say you
got, I wrote one to transform the [Table2].[Ref] values to match the
[Table1].[Ref] values and called it [Q_Table2]; it looks like this:

SELECT 'ref' & [Table2]![Ref] AS Ref, Table2.RefTwo
FROM Table2;

I can then INNER JOIN this to [Table1] and [Table2] as follows:

SELECT Table1.Ref, Q_Table2.RefTwo, Table2.Data
FROM (Q_Table2 INNER JOIN Table1
ON Q_Table2.Ref = Table1.Ref)
INNER JOIN Table2 ON Q_Table2.RefTwo = Table2.RefTwo
WHERE ((Table2.Data)='data3')

UNION

SELECT DISTINCT Table1.Ref, Q_Table2.RefTwo, Null AS Data1
FROM (Q_Table2 INNER JOIN Table1
ON Q_Table2.Ref = Table1.Ref)
INNER JOIN Table2 ON Q_Table2.RefTwo = Table2.RefTwo
WHERE ((Table2.Data)<>'data3')

ORDER BY Table1.Ref;


Even with all this finagling (technical term), the result isn't what you
say SQL Server gave you; the result of this query was the following, in
which the last 2 records didn't match yours:

Ref RefTwo Data
---- ------ -----
ref1 ref21
ref2 ref21
ref3 ref31 data3
ref4 ref41
ref5 ref51
ref6 ref51
ref7 ref51

This is about as close as I could come to the spirit of what you say you
did, without doing something like defining a new table containing your
values and doing a "SELECT * FROM
", which I figure would come
under the category of cheating (not to mention that it would violate
Occam's Razor).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
M

MGFoster

-----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-----
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.
Click to expand...
 
J

John Spencer (MVP)

How about?

SELECT Table1.Ref, Table1.RefTwo, Table2.Data
FROM Table1 LEFT JOIN Table2
-----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-----
Click to expand...
 
Top