Another question on SQL views

[HisLeast]

I'm trying to build a view in MySQL and I'm stuck on what to do when it comes to a table where there are two foreign keys to the same table. The scenario is this: I have a table called TASK which has two columns "assigned_to" and "u_client". One tracks who the task is assigned to, the other tracks who the task is for. Both these columns link to the sys_id field in the USER table (from whence I'm trying to get 'name').

So far my query looks like this...

select T.number, T.sys_class_name, T.short_description, U.name as AssignedName from task T, sys_user U
where T.assigned_to = U.sys_id

... but I'm stumped as to how to add the "client" bit to the select statement. Have I given enough info to make my problem clear? If so, how do I go about working the select statement so I have both the client and assign-to's name in my view?

[tango]

Try something like this:

select t.number, t.sys_class_name, U1.name client, U2.name assigned
from task T, sys_user U1, sys_user U2
where T.assigned_to = u2.sys_id and T.client = U1.sys_id

[HisLeast]

http://img137.echo.cx/img137/8572/aniclapping3wr.gif

That's EXACTLY what the doctor ordered!

[tango]

That was an easy one :P

[HisLeast]

That was an easy one :P

<basking in the radiance of your wisdom>

[HisLeast]

ok... so the script looks like this so far....

select
T.number,
T.sys_class_name,
T.short_description,
U1.name as AssignedTo,
U2.name as ClientName,
U3.name as CreatedBy
from task T, sys_user U1, sys_user U2, sys_user U3
where
T.assigned_to = U1.sys_id
and T.u_client = U2.sys_id
and T.opened_by = U3.sys_id

Now there's a field in the TASK table called Parent which links back to TASK table (since tasks can be subtasks of each other). Parent points back to sys_id in the TASK table. I've tried a couple permutations on my own but I keep making MySQL mad at me! :(

[tango]

How many levels of parents can you have? Is it the kind of thing where each task has one parent and that's it, or are you defining some kind of tree that might go back several levels?

[HisLeast]

How many levels of parents can you have? Is it the kind of thing where each task has one parent and that's it, or are you defining some kind of tree that might go back several levels?

Each record can have only one parent. Of course we can chain them all together (1 is parent of 2 is parent of 3 is parent of 4 etc), but the way I figure it the view is asking each record... "hey, what's your parent". And we get one answer only.

Make sense?

[tango]

OK, so you're defining a tree in the sense that every record has one and only one parent, and some records have no parent at all. No problem there.

What are you wanting to pull out of the table - a complete list of parentage right back to the highest level, or just the one task above the one you're viewing (or a blank if it has no parent)?

[HisLeast]

OK, so you're defining a tree in the sense that every record has one and only one parent, and some records have no parent at all. No problem there.

What are you wanting to pull out of the table - a complete list of parentage right back to the highest level, or just the one task above the one you're viewing (or a blank if it has no parent)?
Maybe it'll help if I make this distinction.
"NUMBER" is the friendly name for a Task's unique identification. Every task has one. Each task also has a sys_id which is a big ugly zillion character long unique ID. Some Tasks have parents, which in the application interface is just the NUMBER of another task.

The problem is that at the table level a Task's "Parent" stores an ID that links back to Task via sys_ID. I want the view to display the number.

So to answer your question (I think), I need each record to display the value of its parent if it has one. I'm not attempting to build a tree or trace dependencies or precedents or anything like that. Just asking for a value.

[tango]

OK, so you're going to need something like this:

select t.details, p.friendlynumber from task t, task p
where t.parent = p.id


This assumes each task has a parent. A task with no parent will be omitted - if you want to include those anyway you'll need to use an outer join - I'm not sure if MySQL permits outer joins. To use them change the = to *= or =*

In the case above I think you want *= but could be wrong on that.

[HisLeast]

OOOOF. I just realized that a good number of the joins I'm executing have the possibility of null values on the other side. So when I ran my full query it came up with like 10% of the records I expected because its filtering out the tickets where client, assignto, closed by, workgroup (the tables I'm joining) are null. :B

So now I'm trying to figure out this outer join thing.

[tango]

Outer joins are fun, but they get pretty ugly pretty quickly.

Whereas your regular inner join works on the basis of returning rows that match (e.g. show me children and parents) the outer join allows a match or null (e.g. show me children and parents, but include the "parents" that have no children).

Fiddle about with the *= and =* operators and you should figure it out. I'm not sure how much of this MySQL lets you do - I've only ever used it for relatively simple stuff. Outer joins, stored procedures etc are the kind of thing I've only ever done with larger-scale back-ends (SQL Server, Sybase, Oracle etc).

[HisLeast]

From what I've been able to Google, the *= doesn't work, but the syntax "LEFT OUTER JOIN" works (source (http://en.wikipedia.org/wiki/Join_%28SQL%29#Natural_join))

I'm currently trying to get it to work in my scripts since I join so many tables. Haven't been able to do it without erroring yet.

[tango]

There's been talk of the *= syntax being deprecated, I think it depends largely on which SQL platform you're using.

I hate LEFT OUTER JOIN, it's so cumbersome by comparison.

[HisLeast]

Yeah... I've tried >= and <= but it acts really strangely, pumping out TONS more records than I expect (like 10 times more before I stop the script).

It looks like I'll have to suck it up and do the left outer joins... I'm just not sure how it works syntactically when you're joining multiple tables.

[HisLeast]

:pp YES!! Here it is in all its glory!!

select
T.number,
T.sys_class_name,
T.short_description,
T.active,
CI.name as CMDB_CI,
C.name as CompanyName,
T.state,
T.priority,
T.approval,
T.close_notes,
T.closed_at,
T.u_reopened as ReopenedDate,
T.u_callcount as callcount,
T.u_resolved_remotely as ResRemote,
T.u_firstcallresolution as isFCR,
T.u_slabreach as slabreach,
T.u_service_ci as ServiceCI,
T.u_createdbygroup as CreatedByGroup,
WG.name as AssignedToGroup,
L.name as Location,
U1.name as AssignedTo,
U2.name as ClientName,
U3.name as CreatedBy,
U4.name as ClosedBy
from task T
left outer join sys_user U1 on (T.assigned_to = U1.sys_id)
left outer join sys_user U2 on (T.u_client = U2.sys_id)
left outer join sys_user U3 on (T.opened_by = U3.sys_id)
left outer join sys_user U4 on (T.closed_by = U4.sys_id)
left outer join sys_user_group WG on (T.assignment_group = WG.sys_id)
left outer join cmn_location L on (T.location = L.sys_id)
left outer join core_company C on (T.company = C.sys_id)
left outer join cmdb_ci CI on (T.cmdb_ci = CI.sys_id)


Unfortunately, I still seem to have 1069 records difference between the # of records in the table (65588) and the number of records in the view (64519). Do you see anything in the above select that would restrict a number of records from the table from being displayed?

[tango]

With everything being an outer join you should be fine. You could try knocking each table out of the query in sequence (remembering to remove the fields from the query!) and running a count on each one.

If there's no corresponding value in the other table the fields should return NULL. If I had to guess which table is going to be causing problems I'd go for U4, if you've got 1069 tasks open that could be the issue.

[HisLeast]

The answer walked up and smacked me in the face 2 minutes ago.
I was validating the query results against what the live database said via the interface. But I'm not working with live data... its about a day old.

DURR!!!

Tango I really appreciate your DBA Crash Course in a Day. :)

[tango]

The answer walked up and smacked me in the face 2 minutes ago.
I was validating the query results against what the live database said via the interface. But I'm not working with live data... its about a day old.

DURR!!!

Tango I really appreciate your DBA Crash Course in a Day. :)

No problem, sounds like I should be talking to your commercial people :P

Seriously, glad it all worked out.