Link to home
Start Free TrialLog in
Avatar of phantem
phantem

asked on

Complex queries

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
ASKER CERTIFIED SOLUTION
Avatar of cheekycj
cheekycj
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of phantem
phantem

ASKER

Thanks!

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

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

CJ
thnx for the "A"

CJ
Avatar of phantem

ASKER

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
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