When you do a normal join, there must be data on both sides of the equation for you to get results. If you're looking for all from one side and data from the other side if you find it, that's an outer join.
Let's start with the main table, movies.
CODE
select
m.id, m.title, m.year, m.runtime,
m.genre,m.language,m.country,
m.director,m.shelf
from movies m
Note the use of the table alias "m". Get used to it, there are more coming.
We're going to be joining on m.id. That means we want another set of data to have one value to match movies.id per row. Well ignore that for the momment and look at the other two tables.
The table rents is where we need to start. We only want one row from it per copy id, so we need to figure the last loan per copy.
CODE
select copy_id, max(loan_date) as loan_date
from rents
group by copy_id
Oops, you have bad data! In your example you loaned out the same copy twice on the same day. For the sake of argument, we'll have to assume greatest id is last record for the copy. So, we'll use this:
CODE
select copy_id, max(id) as id
from rents
group by copy_id
results:
copy_id id
1 2
2 3
3 5
Now that we have one record per copy, we need the data we're really after, the returned state.
CODE
select r.copy_id, r.returned
from rents r
inner join (
select max(id) as id from rents group by copy_id
) rmax on rmax.id=r.id
results:
copy_id returned
1 1
2 0
3 1
Before we move to copies, let's add another copy of a movie.
CODE
id movie_id shelf
4 2 B
Now we're ready to look at copies:
CODE
select c.id, c.movie_id, rtot.returned
from copies c
left outer join (
select r.copy_id, r.returned
from rents r
inner join (
select max(id) as id from rents group by copy_id
) rmax on rmax.id=r.id
) rtot on c.copy_id=rtot.copy_id
results:
id movie_id returned
1 2 1
2 2 0
3 1 1
4 2 NULL
See id 4? That big null? We got that from the outer join. If it had been an inner join, we'd have lost the 4. There are many ways to make that null have a default value, but we don't need it for this exercise. We really care about the number of movies we have. So, here we'll assume NULL means it never left. So, 1 or NULL means we have a copy.
CODE
select c.movie_id, sum(case when rtot.returned=0 then 0 else 1 end) as available
from copies c
left outer join (
select r.copy_id, r.returned
from rents r
inner join (
select max(id) as id from rents group by copy_id
) rmax on rmax.id=r.id
) rtot on c.copy_id=rtot.copy_id
results:
movie_id available
2 2
1 1
Almost there, time to add in the last bit:
CODE
select m.id, m.title, mtot.available
from movies m
left outer join (
select c.movie_id, sum(case when rtot.returned=0 then 0 else 1 end) as available
from copies c
left outer join (
select r.copy_id, r.returned
from rents r
inner join (
select max(id) as id from rents group by copy_id
) rmax on rmax.id=r.id
) rtot on c.copy_id=rtot.copy_id
) mtot on m.id=mtot.movie_id
results:
id title available
1 Robin Hood 1
2 Star Wars 2
3 Bond NULL
You'll want that NULL to be a zero. You can figure that out.
Wow, that's a little more of a complete answer than I planed.
Hope it helps.
7 May, 2008 - 02:48 PM
|