从IN和JOINS访问SQL [英] Access SQL FROM IN and JOINS

问题描述

我已经成功使用

SELECT [field] FROM [Table] IN 'Network Location';

这个问题使我充满了 从另一个数据库访问SQL查询

this question filled me in on that Access SQL Query from another DB

我的问题是:JOIN如何适合此框架

My question is: How does a JOIN fit into this framework

是

SELECT [field] 
FROM [Table] IN 'Network Location' 
JOIN [Table2] 
  ON [Table2].field = [Table].field;

或

SELECT [field]
FROM [Table] 
JOIN [Table2] 
  ON [Table2].field = [Table].field IN 'Network Location' ;

感觉前者是正确的. SQL语句应该像句子，并且感觉最恰当.

it feels like the former is the correct one. SQL statements are supposed to be like sentences and that one feels most appropriately ordered.

推荐答案

考虑方括号或反引号名称限定符，以从外部Access数据库进行查询.从语义上讲，这将遵循其他RBDMS(例如Oracle，Postgres，SQL Server，MySQL，SQLite)期间限定符，以跨同一机器/服务器上的群集，架构，数据库进行查询.从那里开始，使用表别名在SELECT，JOIN，WHERE和其他子句中进行引用.

Consider the bracketed or backticked name qualifier to query from external Access databases. Semantically, this would follow other RBDMS' (e.g., Oracle, Postgres, SQL Server, MySQL, SQLite) period qualifiers to query across clusters, schemas, databases on same machine/server. From there use table aliases for referencing in SELECT, JOIN, WHERE, and other clauses.

SELECT t1.[field] 
FROM [C:\Path\To\External\myDatabase.accdb].[Table] t1
INNER JOIN [Table2] t2
  ON t2.field = t1.field;

SELECT t1.`field` 
FROM `C:\Path\To\External\myDatabase.accdb`.`Table` t1
INNER JOIN `Table2` t2
  ON t2.field = t1.field;

请注意，访问权限要求JOIN是特定的:INNER，LEFT，RIGHT.

Do note, Access requires JOIN to be specific: INNER, LEFT, RIGHT.

由于使用了JET/ACE SQL引擎，您甚至可以以类似的方式查询Excel工作簿和CSV文件，前提是数据在表格范围内是连续的:

And thanks to the JET/ACE SQL engine, you can even query Excel workbooks and CSV files in similar manner assuming data is contiguous in a table-like range:

SELECT *
FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\myWorkbook.xlsx].[SheetName$] AS t;

SELECT t.*
FROM [text;database=C:\Path\To\Folder].myFile.csv AS t;

这篇关于从IN和JOINS访问SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！