英文:
Join table into itself after a union in sqlalchemy
问题
I have two tables:
表一: org,有3列(org_code, chart_code, org_name)
表二: org_2,有2列(org_code, chart_code)
它们代表不同的数据源。
我需要对这两个表进行联合操作,同时从第一张表中获取org_name。
由于这两个表没有完全匹配的列,我认为应该首先在表一和表二上分别进行查询,基于共享的列进行联合查询,然后尝试将查询与第一张表连接起来。
query1 = db.query(org.chart_code, org.org_code).filter(一些筛选条件)
query2 = db.query(org2.chart_code, org2.org_code).filter(一些筛选条件)
union_query = query.union(query2)
联合查询部分运行良好,但在尝试连接时,我无法确定如何指定将org.org_code连接到查询的连接条件。
join_query = union_query.join()
英文:
I have two tables
Table one: org with 3 columns (org_code,chart_code, org_name)
Table two: org_2 with 2 columns (org_code,chart_code)
The represent different sources of data.
I need to do a union on the two but also to get the org_name from the first table.
Since the two don't line-up I figured I should do a union of query on table one and query on table two based on the shared columns and then try to join the query onto the first table
query1 = db.query(org.chart_coded ,org.org_code).filter(some filters)
query2 = db.query(org2.chart_code ,org2.org_code).filter(some filters)
union_query = query.union(query2)
this part of the union works well, but when I am trying to do the join I can't figure out how to specify the join condition of joining org.org_code onto the query.
join_query = union_query.join()
答案1
得分: 1
我不了解SQLAlchemy的语法,但是纯SQL如下:
SELECT org.org_code, org.chart_code, org.org_name
FROM org
UNION
SELECT org2.org_code, org2.chart_code, org1.org_name
FROM org
JOIN org2 ON org.org_code = org2.org_code
这
英文:
I don't know the SQLAlchemy syntax, but the plain SQL is:
SELECT org.org_code, org.chart_code, org.org_name
FROM org
UNION
SELECT org2.org_code, org2.chart_code, org1.org_name
FROM org
JOIN org2 ON org.org_code = org2.org_code
This
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论