Oracle query with multiple tables

oracle

Solution

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_date` to the query conditions.

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.

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_duty` and run that query. Et cetera.

Problem

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. 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:

Original source