Complex queries

phantem
phantem used Ask the Experts™
on
Hi all,

OK, so I've got 2 tables
Audit(UUID,UIDFrom,UIDTo,ChangeType)
and
People(UID,First,Last)

my actual tables are more complex than this, but it will serve for the time being.

What I'm trying to do is to create a query that will provide me with a table showing the content of the audit table, only instead of the UIDFrom, I want to see the UIDFrom person's last name -- and instead of UIDTo, I want that person's last name.

I know that I could just do three separate queries for each row of the audit table, but that seems very ineffieicnt.

Is there a better way to do this?

Thanks!
Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Try this:

select a.uuid, p1.Last, p2.Last, a.ChangeType
from audit a, people p1, people p2
where a.uidFrom = p1.uid
and a.uidTo = p2.uid

CJ

Author

Commented:
Thanks!

I just love quick solutions... I didn't realize that you could re-reference the same table in the from Clause

Chris

Commented:
yup as long as you give it a separate alias like p1 and p2

CJ
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Commented:
thnx for the "A"

CJ

Author

Commented:
CJ,

If there is a row without a value for either the From or To values, that row is not returned... Can you think of a way around this?

The audit table holds 'new' records as well as 'updates', so some records would not have a 'uidFrom'...

Chris

Commented:
you have to do an outer join.

select a.uuid, p1.Last, p2.Last, a.ChangeType
from audit a, people p1, people p2
where a.uidFrom = p1.uid(+)
and a.uidTo = p2.uid(+)

what this will do is if a corresponding row doesn't exist in the people table it will return a null rather than not return anything.

CJ

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial