具有多个表的 Oracle 查询

Oracle query with multiple tables

提问by wholee1

I am trying to display volunteer information with duty and what performance is allocated. I want to display this information. However, when I run the query, it did not gather the different date from same performance. And also availability_date is mixed up. Is it right query for it? I am not sure it is right query. Could you give me some feedback for me? Thanks.

我正在尝试显示有义务的志愿者信息以及分配的绩效。我想显示这些信息。但是，当我运行查询时，它没有从相同的性能中收集不同的日期。而且availability_date 也被混淆了。这是正确的查询吗？我不确定这是正确的查询。你能给我一些反馈吗？谢谢。

Query is here.

查询在这里。

SELECT Production.name, performance.performance_date, volunteer_duty.availability_date, customer.name "Customer volunteer", volunteer.volunteerid, membership.name "Member volunteer", membership.membershipid
FROM Customer, Membership, Volunteer, volunteer_duty, duty, performance_duty, performance, production
WHERE  
Customer.customerId (+) = Membership.customerId AND
Membership.membershipId = Volunteer.membershipId AND
volunteer.volunteerid = volunteer_duty.volunteerid AND
duty.dutyid = volunteer_duty.dutyid AND
volunteer_duty.dutyId = performance_duty.dutyId AND
volunteer_duty.volunteerId = performance_duty.volunteerId AND
performance_duty.performanceId = performance.performanceId AND
Performance.productionId = production.productionId

--Added image-- Result:

--添加图像--结果：

回答by Dave Costa

The query seems reasonable, in terms of it having what appear to be the appropriate join conditions between all the tables. It's not clear to me what issue you are having with the results; it might help if you explained in more detail and/or showed a relevant subset of the data.

该查询似乎是合理的，因为它在所有表之间具有适当的连接条件。我不清楚你对结果有什么问题；如果您更详细地解释和/或显示数据的相关子集，它可能会有所帮助。

However, since you say there is some issue related to availability_date, my first thought is that you want to have some condition on that column, to ensure that a volunteer is available for a given duty on the date of a given performance. This might mean simply adding volunteer_duty.availability_date = performance.performance_dateto the query conditions.

然而，既然你说有一些与 相关的问题availability_date，我的第一个想法是你想在该栏上设置一些条件，以确保志愿者在给定的表演日期可以执行给定的任务。这可能意味着简单地添加volunteer_duty.availability_date = performance.performance_date到查询条件。

My more general recommendation is to start writing the query from scratch, adding one table at a time, and using ANSI join syntax. This will make it clearer which conditions are related to which joins, and if you add one table at a time hopefully you will see the point at which the results are going wrong.

我更一般的建议是从头开始编写查询，一次添加一个表，并使用 ANSI 连接语法。这将使哪些条件与哪些连接相关，并且如果您一次添加一个表，希望您能看到结果出错的点。

For instance, I'd probably start with this:

例如，我可能会从这个开始：

SELECT production.name, performance.performance_date
  FROM production
       JOIN performance ON production.productionid = performance.productionid

If that gives results that make sense, then I would go on to add a join to performance_dutyand run that query. Et cetera.

如果这给出了有意义的结果，那么我将继续添加一个连接performance_duty并运行该查询。等等。

回答by Marc Fischer

I suggest that you explicitly write JOINS, instead of using the WHERE-Syntax.

我建议您明确编写 JOINS，而不是使用 WHERE-Syntax。

Using INNER JOINs the query you are describing, could look like:

使用 INNER JOINs 您所描述的查询，可能如下所示：

SELECT *
FROM volunteer v
INNER JOIN volunteer_duty vd ON(v.volunteerId = vd.colunteerId)
INNER JOIN performance_duty pd ON(vd.dutyId = pd.dutyId AND vd.volunteerId = pd.colunteerId)
INNER JOIN performance p ON (pd.performanceId = p.performanceId)