艰难的SQL目标 - 从4个不同的表中提取数据 [英] Tough SQL objective - pulling data from 4 different tables

问题描述

我有tblVehicles，tblOwners，tblProxies，tblLienholders。

在车辆表中[OwnerID]和[ProxyID]识别

所有者和代理人（通常是律师） 。车辆表是

子表到tblOwners和tblProxies。 tblLienholders有

[VehicleJobID]并且是一张儿童桌到车辆表。


您可以想象，业主，代理人和留置权人的房子

名字&人/公司的地址。在某些时候，我需要

来提取车辆和这些其他实体的动态集 -

每个记录本身。例如，对于给定的车辆 -

表示他们是2个留置权人，所有者和律师代理人。

我想生成4记录的动态集，如下所示：


''79 VW John Q所有者109 Lazy Lane Raleigh NC 27615

''79 VW Alfred Attorney 22 Main St Cary NC 27511

''79大众ABC贷款19 Castle Ave纽约NY 00211

''79大众汽车任务5491明尼苏达州安克雷奇AK 99518


这是否均匀远程可能吗？这是表结构...


tblVehicles tblOwners tblProxies tblLienholders

[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]

[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]

[代理商ID]等等......等等...... [LHaddr]

[VIN]等等......哇......等等......

[MakeModel]等等......等等......等等......
blah ...

blah ...

I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

''79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
''79 VW Alfred Attorney 22 Main St Cary NC 27511
''79 VW ABC Loans 19 Castle Ave New York NY 00211
''79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

推荐答案

>我有tblVehicles，tblOwners，tblProxies，tblLienholders。

>I have tblVehicles, tblOwners, tblProxies, tblLienholders.

在车辆表中，[OwnerID]和[ProxyID]识别
所有者和代理人（通常是律师）。车辆表是儿童表到tblOwners和tblProxies。 tblLienholders有
[VehicleJobID]并且是一张儿童桌到车辆的桌子。

你可以想象，所有者，代理人和留置权人都有名字和名字。人/公司的地址。在某些时候，我需要
提取车辆和这些其他实体的动态集 - 每个都在一个记录中。例如，对于给定的车辆 -
说他们是2个留置权人，所有者和律师代理人。
我想制作一个4记录的动态集如下：

'79大众John Q所有者109 Lazy Lane Raleigh NC 27615
'79大众Alfred Attorney 22 Main St Cary NC 27511
''79大众ABC贷款19 Castle Ave纽约NY 00211
'79大众汽车探索5491明尼苏达州安克雷奇AK 99518

这甚至是远程可能吗？以下是表结构...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID ] [代理ID]等等......等等...... [LHaddr]
[VIN]等等......等等......等等...... [MakeModel]等等......等等......等等......等等......等等......等等...

In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

''79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
''79 VW Alfred Attorney 22 Main St Cary NC 27511
''79 VW ABC Loans 19 Castle Ave New York NY 00211
''79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

我想要什么从2或3个查询开始，然后从这些查询中选择您需要的结果

。尽可能整合。如果需要，请使用查询设计器来获取联接的帮助。

What I would do is start with 2 or 3 queries and select the results you need
from those queries. Consolidate as you can. Use the query designer for
help with the joins if you need to.

如果一个查询无法完成，我会必须使用DAO -

手动记录记录。不会那么优雅。很多

而不是从SQL做。

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

If it cannot be done with one query, I''ll have to do it using DAO -
walking the records manually. Won''t be as elegant. Would much
rather do it from SQL.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

我要做的是从2开始或者3查询并从这些查询中选择您需要的结果。尽可能整合。如果需要，可以使用查询设计器来获取联接帮助。

What I would do is start with 2 or 3 queries and select the results you need
from those queries. Consolidate as you can. Use the query designer for
help with the joins if you need to.

2005年9月26日星期一22:49 ：56 -0400，MLH< CR ** @ NorthState.net>写道：


听起来很可能，最有可能使用4部分联合查询。每个部分

追求一种类型的实体。我建议添加一个专栏

来识别实体类型。


-Tom。

On Mon, 26 Sep 2005 22:49:56 -0400, MLH <CR**@NorthState.net> wrote:

Sounds possible, most likely using a 4-part union query. Each part
goes after one type of entity. I would recommend adding a column just
to identify the entitytype.

-Tom.

我有tblVehicles，tblOwners，tblProxies，tblLienholders。
在车辆表中[OwnerID]和[ProxyID]识别
所有者和代理人（通常是律师）。车辆表是儿童表到tblOwners和tblProxies。 tblLienholders有
[VehicleJobID]并且是一张儿童桌到车辆的桌子。

你可以想象，所有者，代理人和留置权人都有名字和名字。人/公司的地址。在某些时候，我需要
提取车辆和这些其他实体的动态集 - 每个都在一个记录中。例如，对于给定的车辆 -
说他们是2个留置权人，所有者和律师代理人。
我想制作一个4记录的动态集如下：

'79大众John Q所有者109 Lazy Lane Raleigh NC 27615
'79大众Alfred Attorney 22 Main St Cary NC 27511
''79大众ABC贷款19 Castle Ave纽约NY 00211
'79大众汽车探索5491明尼苏达州安克雷奇AK 99518

这甚至是远程可能吗？以下是表结构...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID ] [代理ID]等等......等等...... [LHaddr]
[VIN]等等......等等......等等...... [MakeModel]等等......等等......等等......等等......等等......等等......

I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are [OwnerID] and [ProxyID] identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
[VehicleJobID] and is a child table to vehicles table.

As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other entities -
each in a record by itself. For instance, for a given vehicle -
say their were 2 lienholders, an owner and an attorney proxy.
I would like to produce a 4-record dynaset as follows:

''79 VW John Q Owner 109 Lazy Lane Raleigh NC 27615
''79 VW Alfred Attorney 22 Main St Cary NC 27511
''79 VW ABC Loans 19 Castle Ave New York NY 00211
''79 VW Car Quest 5491 Minnesota Anchorage AK 99518

Is that even remotely possible? Here are table structures...

tblVehicles tblOwners tblProxies tblLienholders
[VehicleJobID] [OwnerID] [ProxyID] [LienHolderID]
[OwnerID] [OwnAddr] [ProxAddr] [VehicleJobID]
[ProxyID] blah... blah... [LHaddr]
[VIN] blah... blah... blah...
[MakeModel] blah... blah... blah...
blah...
blah...
blah...

这篇关于艰难的SQL目标 - 从4个不同的表中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！