Need help - query with joins within same table...

[Ann S]

I've been struggling with this one, and I don't know if it can be done.

I have two tables. One has information about activity - call it Metrics. The other has information about the object that was selected in the previous table - call it Weblog.

Metrics has three important fields: object (which is always a number), Loc (usually a number prefaced by a u), and activity (text).
Weblog has several fields:
Entry (which I can link to Metrics.object)
Type (User, Content, Location, Master)
Name (name of the content)
Master_id (numeric identification of the master location)
Master_name (name of the master location)

Several types of information is kept in this table, depending on the Type: User (who owns the Master), Content, Location (where the content is located) and Master (the Master where several Locations may reside).

An Example of the Weblog table:
|entry | Type | Name | Master_id | Master_name |
|38 | Master | Poodle | 26 | PoodleHat |
|148 | Content | Poodle's WebStore | 26 | PoodleHat |
|130 | Location | Products | 38 | PoodleHat |
|u26 | User | [e-mail] | 0 | {null} |
|26 | Master | Kitty | 15 | KittyLitter |
|120 | Location | Theories | 26 | KittyLitter |
|u15 | User | [e-mail] | 0 | {null} |
|u38 | User | [e-mail] | 0 | {null} |

I can link Metrics.object to Weblog.entry and get the name of the content. What I need now (on the same line) is to get the Location and the Master on the same line, so that I know that Poodle's Webstore belongs to the Products location and the PoodleHat Master.

The problem that comes is this: the User that owns PoodleHat is prefaced by a u. I can't drop that in a query - if I do and link on master_id to entry, I get a different Master_name and Location because u26 is a totally different owner.

Here's what I have so far:
select count(r.object), r.activity, r.object, r.loc, z.name as ContentName,
z.Master_name as MasterLocation, m.name as Location, z.Master_id
from Metrics as r
inner join Weblog as z on r.object = z.entry
inner join Weblog as m on z.owner_id = m.entry_id
where z.type= ('Location') and r.activity in ( 'View', 'Download')
group by r.object order by r.activity, z.Master_name

Any ideas?