Re: HOWTO: Use conditions in FROM clause in Access sintax.
- From: Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 08 Sep 2005 15:17:42 GMT
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.
There's more than just a syntactic problem (see below).
[...]
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)
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 [Table]", which I figure would come under the category of cheating (not to mention that it would violate Occam's Razor).
-- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx> Please feel free to quote anything I say here. .
- References:
- Prev by Date: Adding records from a table and a query
- Next by Date: Re: How to set up default number format in a make-table query?
- 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
|
|
