Oracle query with multiple tables

Question

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.

enter image description here

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: enter image description here

Dave Costa · Accepted Answer · 2012-04-03 20:37:55Z

6

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.

edited Apr 3, 2012 at 20:37

answered Apr 3, 2012 at 20:08

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wholee1 Over a year ago

Thank you for replying. When I try to use your query, it is working, but how can I use all tables by using ANSI join syntax? Could you give me some query for me? I added the last result which had problem.. please let me know. thank you.

Marc Fischer · Accepted Answer · 2012-04-03 20:07:49Z

3

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)

answered Apr 3, 2012 at 20:07

Marc Fischer

1,3061 gold badge13 silver badges16 bronze badges

1 Comment

Ollie Over a year ago

+1, poeple should be using the SQL-92 syntax and not still clinging on to the outdated SQL-86 syntax.

Collectives™ on Stack Overflow

Oracle query with multiple tables

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related