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:
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.
I suggest that you explicitly write JOINS, instead of using the WHERE-Syntax.
Using INNER JOINs the query you are describing, could look like:
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)